const express = require("express");
const router = express.Router();
const path = require("path");
const utils = require("../utils/index.js");
const utilsServer = require("../utils/serverInfo.js");
const moment = require("moment-timezone");
const multer = require("multer");
const AdmZip = require("adm-zip");
const AppInfoParser = require("app-info-parser");
const plist = require("plist");
const fs = require("fs");
const {
    readManifest
} = require("node-aab-parser");
const msgUtils = require("../utils/feishu.js");
const crypto = require("crypto");
const CryptoJS = require("crypto-js");
const FormData = require("form-data");
const {
    exec
} = require("child_process");
const config = require("../config");
const axios = require("axios");
const jwt = require("jsonwebtoken");
const jwksClient = require('jwks-rsa');
// const appleSignin = require('apple-signin-auth');
const {
    promisify
} = require('util');
const jwtVerifyAsync = promisify(jwt.verify);
const poolmomo = require("../poolmomo.js");

// 配置 multer 的存储设置
const storage = multer.diskStorage({
    destination: function (req, file, cb) {
        // 设置文件上传目录
        cb(null, "/home/wwwroot/default/TransirImages");
    },
    filename: function (req, file, cb) {
        // 生成文件名
        cb(null, Date.now() + "-" + file.originalname);
    },
});

const upload = multer({ storage: storage });

// 创建文件上传的 POST 接口
router.post("/upload", upload.single("file"), (req, res) => {
    const file = req.file;
    if (!file) {
        return res.status(400).send("请上传一个文件。");
    }
    let fileUrl = ''
    fileUrl = `https://xiaoshuchengzhang.com/TransirImages/${file.filename}`;
    // 返回文件存储地址
    res.send({
        code: 0,
        message: "success",
        fileUrl: fileUrl,
    });
});



/**
 * 获取项目配置信息
 * GET /api/config
 * 直接从transir_system_configs表读取第一项作为全局配置
 */
router.get('/config', async (req, res) => {
    try {
        // 取数据库第一条配置（通常按id排序取第一个）
        const sql = 'SELECT * FROM transir_system_configs';
        const configs = await mysqlQuery(sql, []);
        const config = configs[0]; // 取第一项
        // 返回数据
        res.send({
            code: 0,
            message: 'success',
            data: config
        });
    } catch (err) {
        // 异常捕获
        console.error('查询项目配置异常:', err);
        res.status(500).json({
            code: 500,
            message: '服务器异常'
        });
    }
});

/**
 * 强制保留1位小数，所有momo币相关数量都必须用
 * @param n 任意可数值参数
 * @returns float 保留1位
 */
function toDecimal1(n) {
    return Math.round(Number(n) * 10) / 10;
}
/**
 * 检查/创建用户（苹果账号登录专用）
 * @param {string} appleIdentifier - Apple的唯一用户ID (即JWT的sub)
 * @returns {Promise<Object>} - 用户资料行
 */
// 工具：获取指定北京时间（标准格式字符串）
function toBeijingStr(ts) {
    if (!ts) return null;
    return moment(Number(ts)).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
}

// 工具：获取会员配额参数（返回 {feature_minutes, photos}）
async function getMemberConfig() {
    const configSql = 'SELECT * FROM transir_system_configs LIMIT 1';
    const configs = await mysqlQuery(configSql, []);
    if (configs.length === 0) return {
        feature_minutes: 0,
        photos: 0
    };
    return {
        feature_minutes: configs[0].member_feature_minutes || 0,
        photos: configs[0].member_photos_limit || 0,
        tokens: configs[0].member_tokens_limit || 0,
        float_copy: configs[0].member_float_copy_limit || 0,
        quick_text: configs[0].member_quick_text_limit || 0
    };
}

// 工具：获取普通用户配额参数（返回 {feature_minutes, photos}）
async function getRegularConfig() {
    const configSql = 'SELECT * FROM transir_system_configs LIMIT 1';
    const configs = await mysqlQuery(configSql, []);
    if (configs.length === 0) return {
        feature_minutes: 0,
        photos: 0
    };
    return {
        feature_minutes: configs[0].regular_feature_minutes || 0,
        photos: configs[0].regular_photos_limit || 0,
        tokens: configs[0].regular_tokens_limit || 0,
        float_copy: configs[0].regular_float_copy_limit || 0,
        quick_text: configs[0].regular_quick_text_limit || 0
    };
}

// --------- 以下为主逻辑 --------
// 检查当前时间是否在临时送会员区间
function isTemporaryMemberPeriod() {
    // 起止时间都用北京时间，范围闭区间，结束为 2025-06-18 00:00:00 (含当日零点)
    const start = moment.tz('2025-05-28 00:00:00', "Asia/Shanghai").valueOf();
    const end = moment.tz('2025-06-18 00:00:00', "Asia/Shanghai").valueOf();

    const now = moment.tz(getNowInBeijing(), "Asia/Shanghai").valueOf();

    return now >= start && now < end;
}
// 工具二：判断是否为新版本“6.18～6.22十天pro临时会员”区间
function isProTemporaryMemberPeriod() {
    // 2025-06-18 00:00:00 ～ 2025-06-22 00:00:00
    const proStart = moment.tz('2025-06-18 00:00:00', "Asia/Shanghai").valueOf();
    const proEnd = moment.tz('2025-06-22 00:00:00', "Asia/Shanghai").valueOf();
    const now = moment.tz(getNowInBeijing(), "Asia/Shanghai").valueOf();
    return now >= proStart && now < proEnd;
}
// 专用到期时间生成函数：新区间pro临时会员到期= 当前（北京时间）+10天，转北京时间字符串
function proTemporaryMemberExpiry(nowStr) {
    // nowStr 建议直接传 getNowInBeijing() 的结果
    return moment.tz(nowStr, "Asia/Shanghai").add(10, 'days').format("YYYY-MM-DD HH:mm:ss");
}
// 生成本次“临时会员”到期时间：2025-06-18 00:00:00 北京时间字符串
function temporaryMemberExpiry() {
    return moment.tz('2025-06-18 00:00:00', "Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
}
async function checkOrCreateUserByApple(appleIdentifier, user_demand = null, user_source_channel = null) {
    // 1. 查找是否已经注册
    const querySql = 'SELECT * FROM transir_users WHERE apple_identifier = ? LIMIT 1';
    let result = await mysqlQuery(querySql, [appleIdentifier]);

    if (result.length > 0) {
        // 已注册用户 —— 登录刷新时间
        const updateSql = `
        UPDATE transir_users
        SET last_login_time = NOW()
            ${user_demand ? ', user_demand = ?' : ''}
            ${user_source_channel ? ', user_source_channel = ?' : ''}
        WHERE user_id = ?
    `;
        // 构建参数
        let params = [];
        if (user_demand) params.push(JSON.stringify(user_demand));
        if (user_source_channel) params.push(JSON.stringify(user_source_channel));
        params.push(result[0].user_id);

        await mysqlQuery(updateSql, params);
        // 时间格式化
        const user = result[0];
        user.isNew = '1'
        user.registration_time = moment(user.registration_time).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
        user.last_login_time = moment(user.last_login_time).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
        user.usage_base_date = moment(user.usage_base_date).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
        user.last_usage_reset_date = moment(user.last_usage_reset_date).tz("Asia/Shanghai").format(
            "YYYY-MM-DD HH:mm:ss");
        user.next_usage_reset_date = moment(user.next_usage_reset_date).tz("Asia/Shanghai").format(
            "YYYY-MM-DD HH:mm:ss");
        user.created_at = user.created_at ? moment(user.created_at).tz("Asia/Shanghai").format(
            "YYYY-MM-DD HH:mm:ss") : null;
        user.updated_at = user.updated_at ? moment(user.updated_at).tz("Asia/Shanghai").format(
            "YYYY-MM-DD HH:mm:ss") : null;
        return user;
    } else {
        // ----------- 新增：注销快照表反滥用注册判断 -----------
        // 查询transir_users_deleted表，有没有超3次注销
        const delRows = await mysqlQuery(
            'SELECT COUNT(*) as cnt FROM transir_users_deleted WHERE apple_identifier = ?',
            [appleIdentifier]
        );
        if (delRows && delRows[0] && delRows[0].cnt >= 3) {
            // 超过限制，不允许注册
            // 你可以自定义code和message
            return {
                code: 1002,
                message: '因多次注销账号，已被限制注册。如有疑问请联系客服。'
            }
        }
        // ----------- ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ -----------
        // 新用户注册流程

        // 生成默认用户名
        const username = await generateUniqueUsername(); // 确保不重名
        // 生成头像名
        const iconList = ['icon1', 'icon2', 'icon3', 'icon4'];
        const avatarName = iconList[Math.floor(Math.random() * iconList.length)];
        // 生成邀请码
        const inviteCode = await generateInviteCode();
        // 生成用户ID
        const user_id = utils.createId();
        // 所有时间字段，转为北京时间
        const nowStr = getNowInBeijing();

        // ================== 判断是否处于临时会员活动期间 ==================
        let user_type = 'regular'; // 默认为普通用户
        let featureMinutes = 0,
            photos = 0,
            tokens = 0,
            floatCopy = 0,
            quickText = 0;
        let featureMinutesLeft = 0,
            photosLeft = 0,
            tokensLeft = 0,
            floatCopyLeft = 0,
            quickTextLeft = 0;
        let subscription_start_date = null,
            subscription_expiry_date = null;
        let usage_base_date = nowStr,
            lastUsageResetDate = nowStr,
            nextUsageResetDate = null;
        //--- 新增逻辑：优先判断 pro 临时会员
        if (isProTemporaryMemberPeriod()) {
            // ======================== 新增区间 6.18-6.22 新用户送10天pro临时会员 ==================
            user_type = 'temporary_member';
            // 可以用会员配置，或者如果你有更高pro配额的配置可以在此获取
            const proMemberConf = await getMemberConfig();
            featureMinutes = featureMinutesLeft = proMemberConf.feature_minutes;
            photos = photosLeft = proMemberConf.photos;
            tokens = tokensLeft = proMemberConf.tokens;
            floatCopy = floatCopyLeft = proMemberConf.float_copy;
            quickText = quickTextLeft = proMemberConf.quick_text;

            subscription_start_date = nowStr; // 注册时
            subscription_expiry_date = proTemporaryMemberExpiry(nowStr); // 当前时间加10天
            lastUsageResetDate = nowStr;
            usage_base_date = nowStr;
            nextUsageResetDate = subscription_expiry_date; // 到期时
        } else if (isTemporaryMemberPeriod()) {
            // ======================== 老区间 5.28-6.18 送会员（7天或按配置） ==================
            user_type = 'temporary_member';
            const memberConf = await getMemberConfig();
            featureMinutes = featureMinutesLeft = memberConf.feature_minutes;
            photos = photosLeft = memberConf.photos;
            tokens = tokensLeft = memberConf.tokens;
            floatCopy = floatCopyLeft = memberConf.float_copy;
            quickText = quickTextLeft = memberConf.quick_text;
            subscription_start_date = nowStr;
            subscription_expiry_date = temporaryMemberExpiry(); // 6.18 00:00
            lastUsageResetDate = nowStr;
            usage_base_date = nowStr;
            nextUsageResetDate = temporaryMemberExpiry();
        } else {
            // 普通注册
            const regConf = await getRegularConfig();
            featureMinutes = featureMinutesLeft = regConf.feature_minutes;
            photos = photosLeft = regConf.photos;
            tokens = tokensLeft = regConf.tokens;
            floatCopy = floatCopyLeft = regConf.float_copy;
            quickText = quickTextLeft = regConf.quick_text;
            lastUsageResetDate = nowStr;
            usage_base_date = nowStr;
            nextUsageResetDate = moment(nowStr).add(1, 'month').format("YYYY-MM-DD HH:mm:ss");
        }

        // INSERT 新用户时，把这两个字段塞进去
        const insertSql = `
    INSERT INTO transir_users(
        user_id, auth_type, apple_identifier, username, avatar_url,
        feature_minutes, feature_minutes_left, photos, photos_left, tokens, tokens_left, float_copy, float_copy_left, quick_text, quick_text_left, invitation_code,
        registration_time, last_login_time, usage_base_date, last_usage_reset_date, next_usage_reset_date,
        created_at, updated_at,
        user_type,
        subscription_start_date, subscription_expiry_date,
        user_demand, user_source_channel
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`;
        await mysqlQuery(insertSql, [
            user_id,
            'apple',
            appleIdentifier,
            username,
            avatarName,
            featureMinutes,
            featureMinutesLeft,
            photos,
            photosLeft,

            tokens,
            tokensLeft,
            floatCopy,
            floatCopyLeft,
            quickText,
            quickTextLeft,

            inviteCode,
            nowStr, // registration_time
            nowStr, // last_login_time
            usage_base_date,
            lastUsageResetDate,
            nextUsageResetDate,
            nowStr, // created_at
            nowStr, // updated_at
            user_type,
            subscription_start_date,
            subscription_expiry_date,
            user_demand ? JSON.stringify(user_demand) : null,           // 序列化json字符串
            user_source_channel ? JSON.stringify(user_source_channel) : null
        ]);
        // // === 【这个地方插入快照恢复代码】===
        // await recoverUserFromSnapshot({
        //     user_id: user_id,
        //     wechat_identifier: null, // 这里微信登录传
        //     apple_identifier: appleIdentifier
        // });
        // 拉数据返回（已确保时间已格式化）
        const users = await mysqlQuery('SELECT * FROM transir_users WHERE user_id = ?', [user_id]);
        const user = users[0];
        user.isNew = '0'
        // 赋值辅助字段
        user.feature_minutes_left = featureMinutesLeft;
        user.photos_left = photosLeft;

        user.tokens_left = tokensLeft;
        user.float_copy_left = floatCopyLeft;
        user.quick_text_left = quickTextLeft;

        user.registration_time = nowStr;
        user.last_login_time = nowStr;
        user.usage_base_date = usage_base_date;
        user.last_usage_reset_date = lastUsageResetDate;
        user.next_usage_reset_date = nextUsageResetDate;
        user.created_at = nowStr;
        user.updated_at = nowStr;
        user.user_type = user_type;
        user.subscription_start_date = subscription_start_date;
        user.subscription_expiry_date = subscription_expiry_date;
        return user;
    }
}

router.post('/grantTemporaryMember', async (req, res) => {
    const {
        user_id
    } = req.body;
    const users = await mysqlQuery('SELECT * FROM transir_users WHERE user_id = ? LIMIT 1', [user_id]);
    if (!users || users.length === 0) {
        return { code: 404, message: '用户不存在' };
    }

    // 2. 会员权益配置取最新
    const memberConf = await getMemberConfig();

    const nowStr = getNowInBeijing();
    const expiryStr = moment(nowStr).add(1, 'months').format('YYYY-MM-DD HH:mm:ss');
    // const expiryStr = moment(nowStr).add(15, 'days').format('YYYY-MM-DD HH:mm:ss');

    // 3. 更新会员相关字段
    const updateSql = `
        UPDATE transir_users
        SET 
            user_type = 'temporary_member',
            feature_minutes = ?,
            feature_minutes_left = ?,
            photos = ?,
            photos_left = ?,
            tokens = ?,
            tokens_left = ?,
            float_copy = ?,
            float_copy_left = ?,
            quick_text = ?,
            quick_text_left = ?,
            subscription_start_date = ?,
            subscription_expiry_date = ?,
            last_usage_reset_date = ?,
            usage_base_date = ?,
            next_usage_reset_date = ?,
            updated_at = ?
        WHERE user_id = ?
    `;
    const params = [
        memberConf.feature_minutes, memberConf.feature_minutes,
        memberConf.photos, memberConf.photos,
        memberConf.tokens, memberConf.tokens,
        memberConf.float_copy, memberConf.float_copy,
        memberConf.quick_text, memberConf.quick_text,
        nowStr,               // subscription_start_date
        expiryStr,            // subscription_expiry_date
        nowStr,               // last_usage_reset_date
        nowStr,               // usage_base_date
        expiryStr,            // next_usage_reset_date
        nowStr,               // updated_at
        user_id
    ];
    await mysqlQuery(updateSql, params);

    // 4. 返回用户最新信息
    const updatedUser = await mysqlQuery('SELECT * FROM transir_users WHERE user_id = ?', [user_id]);
    res.send({ data: updatedUser })
})
/**
 * 用户领取补发10天pro会员接口
 * 只在2025-06-18 00:00:00 ～ 2025-06-22 00:00:00之间可用。
 * 入口：POST /benefit/beta_user_claim
 * body: { user_id }
 */
router.post('/benefit/beta_user_claim', async (req, res) => {
    try {
        const {
            user_id
        } = req.body;
        if (!user_id) {
            return res.status(400).json({
                code: 400,
                message: "缺少user_id"
            });
        }
        // 1. 判断当前是否处于 pro 临时会员赠送区间
        if (!isProTemporaryMemberPeriod()) {
            return res.send({
                code: 1,
                message: "不在pro临时会员赠送时间段"
            });
        }

        // 2. 查询用户信息
        const userList = await mysqlQuery('SELECT * FROM transir_users WHERE user_id = ?', [user_id]);
        if (userList.length === 0) {
            return res.send({
                code: 202,
                message: '用户不存在'
            });
        }
        const user = userList[0];

        // 3. 查查这个用户有没有领过（用充值记录判重）
        const ACTIVITY_PRODUCT_ID = 'pro_member_compensate_20250618'; // 以后每次补偿都变动！
        const rechargeList = await mysqlQuery(
            `SELECT id FROM transir_recharge_records WHERE user_id = ? AND product_id = ? LIMIT 1`,
            [user_id, ACTIVITY_PRODUCT_ID]
        );
        if (rechargeList.length > 0) {
            // 领过，直接返回，不再重复发放
            return res.send({
                code: 2,
                message: "会员补发和momo币只能领取一次，不能重复领取"
            });
        }

        // 4. 判断注册时间是否在5.28-6.18老临时会员区间
        let regTimeStr = user.registration_time;
        const regTime = moment.tz(regTimeStr, "Asia/Shanghai").valueOf();
        const oldStart = moment.tz('2025-05-28 00:00:00', "Asia/Shanghai").valueOf();
        const oldEnd = moment.tz('2025-06-18 00:00:00', "Asia/Shanghai").valueOf();

        if (!(regTime >= oldStart && regTime < oldEnd)) {
            // 不在老会员时间段内，直接正常返回
            return res.send({
                code: 1,
                message: "用户注册时间不在可补发范围内"
            });
        }

        // 5. 给用户更新10天会员 并 送160 momo币
        const nowStr = getNowInBeijing(); // 指定当前北京时间字符串
        const expiryStr = proTemporaryMemberExpiry(nowStr); // now+10天

        const memberConf = await getMemberConfig();
        // 判断原到期时间（不缩短，只延长）
        let oriExp = user.subscription_expiry_date;
        let needUpdate = true;
        if (oriExp && moment.tz(oriExp, "Asia/Shanghai").valueOf() > moment.tz(expiryStr, "Asia/Shanghai")
            .valueOf()) {
            needUpdate = false;
        }

        // 5.1 一定“只允许领取一次”，所以会员也不重复补发
        if (needUpdate) {
            // 会员相关字段
            const updateSql = `
                UPDATE transir_users SET 
                    user_type = ?,
                    feature_minutes = ?, feature_minutes_left = ?,
                    photos = ?, photos_left = ?,
                    tokens = ?, tokens_left = ?,
                    float_copy = ?, float_copy_left = ?,
                    quick_text = ?, quick_text_left = ?,
                    subscription_start_date = ?,
                    subscription_expiry_date = ?,
                    usage_base_date = ?,
                    last_usage_reset_date = ?,
                    next_usage_reset_date = ?,
                    updated_at = ?
                WHERE user_id = ?
            `;
            await mysqlQuery(updateSql, [
                'temporary_member',
                memberConf.feature_minutes,
                memberConf.feature_minutes,
                memberConf.photos,
                memberConf.photos,
                memberConf.tokens,
                memberConf.tokens,
                memberConf.float_copy,
                memberConf.float_copy,
                memberConf.quick_text,
                memberConf.quick_text,
                nowStr, // subscription_start_date
                expiryStr, // subscription_expiry_date
                nowStr, // usage_base_date
                nowStr, // last_usage_reset_date
                expiryStr, // next_usage_reset_date
                nowStr, // updated_at
                user_id
            ]);
        }

        // 5.2 送160 momo币（见下方注释，既然判重了，不怕重复）
        const momoAmount = 160;
        const expireTime = moment.tz(nowStr, "Asia/Shanghai").add(1, 'years').format("YYYY-MM-DD HH:mm:ss");
        const rechargeInsertSQL = `
            INSERT INTO transir_recharge_records (
                user_id, amount, product_id, remain, recharge_time, expire_time, status, type
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        `;
        await mysqlQuery(rechargeInsertSQL, [
            user_id,
            momoAmount,
            ACTIVITY_PRODUCT_ID, // 业务用，便于区分赠送/活动
            momoAmount,
            nowStr,
            expireTime,
            'unused', // 尚未消费
            '2' // 这里作为type=2标记写入pk字段（如需扩展建议升级为真正的type字段）
        ]);
        // 同步用户总余额
        await mysqlQuery(
            `UPDATE transir_users SET momo_balance = COALESCE(momo_balance,0) + ? WHERE user_id = ?`,
            [momoAmount, user_id]
        );

        // 6. 返回最新用户内容
        const newUserList = await mysqlQuery('SELECT * FROM transir_users WHERE user_id = ?', [user_id]);
        if (newUserList.length === 0) {
            return res.status(500).json({
                code: 500,
                message: '补发后查询用户失败'
            });
        }
        const newUser = newUserList[0];
        // 时间字段全部北京时间格式化
        newUser.registration_time = moment(newUser.registration_time).tz("Asia/Shanghai").format(
            "YYYY-MM-DD HH:mm:ss");
        newUser.last_login_time = moment(newUser.last_login_time).tz("Asia/Shanghai").format(
            "YYYY-MM-DD HH:mm:ss");
        newUser.usage_base_date = moment(newUser.usage_base_date).tz("Asia/Shanghai").format(
            "YYYY-MM-DD HH:mm:ss");
        newUser.last_usage_reset_date = moment(newUser.last_usage_reset_date).tz("Asia/Shanghai").format(
            "YYYY-MM-DD HH:mm:ss");
        newUser.next_usage_reset_date = moment(newUser.next_usage_reset_date).tz("Asia/Shanghai").format(
            "YYYY-MM-DD HH:mm:ss");
        newUser.created_at = newUser.created_at ? moment(newUser.created_at).tz("Asia/Shanghai").format(
            "YYYY-MM-DD HH:mm:ss") : null;
        newUser.updated_at = newUser.updated_at ? moment(newUser.updated_at).tz("Asia/Shanghai").format(
            "YYYY-MM-DD HH:mm:ss") : null;

        return res.send({
            code: 0,
            message: "pro会员赠送/补发成功",
            data: newUser
        });
    } catch (e) {
        console.error('pro会员补发异常:', e);
        res.status(500).json({
            code: 500,
            message: '服务器异常'
        });
    }
});



/**
 * 生成形如 用户-xxxxxx 的随机昵称
 * @returns {string}
 */
function generateDefaultUsername() {
    // 方法一：使用6位随机数字
    // const randNum = Math.floor(Math.random() * 1000000); // 0-999999
    // return `用户-${randNum.toString().padStart(6, '0')}`;

    // 方法二：使用6位随机小写字母+数字组合
    const chars = 'abcdefghijklmnopqrstuvwxyz0123456789';
    let randStr = '';
    for (let i = 0; i < 6; i++) {
        randStr += chars[Math.floor(Math.random() * chars.length)];
    }
    return `用户-${randStr}`;
}
/**
 * 生成没有被占用的唯一用户名
 * 步骤: 循环生成随机名, 查库, 若存在则重试, 最多尝试20次
 */
async function generateUniqueUsername() {
    let maxTries = 50;
    for (let i = 0; i < maxTries; i++) {
        const newName = generateDefaultUsername();
        // 查询数据库用户名是否存在
        const users = await mysqlQuery('SELECT 1 FROM transir_users WHERE username = ? LIMIT 1', [newName]);
        if (!users.length) {
            // 数据库没有该用户名, 可用
            return newName;
        }
        // 有重名的话会自动重试
    }
    throw new Error('注册失败：无法生成唯一昵称，请重试!');
}

/**
 * 苹果登录接口 
 * POST /api/login/apple
 * body: { identityToken: string }             
 */
router.post('/login/apple', async (req, res) => {
    try {
        const {
            identityToken,
            user_demand, // 新增：用户诉求
            user_source_channel // 新增：获知渠道
        } = req.body;
        if (!identityToken) {
            return res.status(400).json({
                code: 400,
                message: "缺少identityToken参数"
            });
        }
        // // 第一步，仅解密拿aud字段（bundleId），判断请求 App 身份
        // const tmpDecodedJwt = jwt.decode(identityToken);
        // console.log(tmpDecodedJwt)
        // const currentBundleId = tmpDecodedJwt && tmpDecodedJwt.aud;
        // if (!currentBundleId) {
        //     return res.status(400).json({ code: 400, message: "无效identityToken, 未发现aud(bundleId)" });
        // }

        // // 第二步，正式用apple-signin-auth校验identityToken有效性
        // let payload;
        // try {
        //     payload = await appleSignin.verifyIdToken(identityToken, {
        //         audience: currentBundleId,
        //         ignoreExpiration: false
        //     });
        // } catch (err) {
        //     console.error("Apple登录校验失败:", err);
        //     return res.status(401).json({ code: 401, message: "Apple身份校验失败" });
        // }

        // // 取得唯一码 apple 用户id（sub字段）
        // const appleIdentifier = payload.sub;
        // if (!appleIdentifier) {
        //     return res.status(500).json({ code: 500, message: "Apple身份Token无sub" });
        // }
        // 查找或创建用户
        const user = await checkOrCreateUserByApple(identityToken, user_demand, user_source_channel);
        // 这里判断 user 是否带有错误 code
        if (user && user.code === 1002) {
            return res.send(user);
        }
        // 可选：生成你自己的Token（JWT或session，可选实现）
        // const myToken = createJWT(user);

        // 返回用户相关信息
        res.send({
            code: 0,
            message: 'success',
            data: user
        })
    } catch (e) {
        console.error('苹果登录异常:', e);
        res.status(500).json({
            code: 500,
            message: '服务器异常'
        });
    }
});
// 获取用户信息，同时如果资源已到期自动刷新资源用量
router.post('/userInfo', async (req, res) => {
    const {
        user_id
    } = req.body;

    // 1. 参数校验
    if (!user_id) {
        return res.status(400).json({
            code: 400,
            message: '缺少user_id'
        });
    }

    try {
        // 2. 查询用户
        const sql = 'SELECT * FROM transir_users WHERE user_id = ? LIMIT 1';
        const result = await mysqlQuery(sql, [user_id]);
        if (result.length === 0) {
            return res.status(202).json({
                code: 202,
                message: '用户不存在'
            });
        }
        let user = result[0];
        // =========== 【新增：立即更新活跃时间】 ===========
        await mysqlQuery(
            'UPDATE transir_users SET last_active_time=? WHERE user_id=?',
            [getNowInBeijing(), user_id]
        );
        user.last_active_time = getNowInBeijing(); // 前端数据也用这个
        // =========== 【新增：立即更新活跃时间】 ===========
        // 检查和刷新已过期的充值记录
        await refreshExpiredMomoRecharge1(user_id);

        // ***【新增：检查temporary_member订阅过期并降级】***
        const currentTime = moment().tz("Asia/Shanghai").valueOf();
        const subscriptionExpiryDate = user.subscription_expiry_date ?
            moment(user.subscription_expiry_date).tz("Asia/Shanghai").valueOf() :
            null;

        if (
            user.user_type === 'temporary_member' &&
            (!subscriptionExpiryDate || subscriptionExpiryDate < currentTime)
        ) {
            // 拉取普通用户（regular）的用量参数配置
            const configSql = `
                    SELECT 
                        regular_feature_minutes AS feature_minutes, 
                        regular_photos_limit AS photos,
                        regular_tokens_limit AS tokens,
                        regular_float_copy_limit AS float_copy,
                        regular_quick_text_limit AS quick_text
                    FROM transir_system_configs 
                    LIMIT 1
                `;
            const [config] = await mysqlQuery(configSql, []);

            // 解构出各项配置
            const {
                feature_minutes,
                photos,          // 这个就是 regular_photos_limit
                tokens,
                float_copy,
                quick_text
            } = config;

            // 记录普通用户照片配额
            const regular_photos_limit = config.photos || 0;

            // 拿用户历史使用数（你表里 regular_photos_used 字段记录的是普通用户时的已用数量）
            const regular_photos_used = user.regular_photos_used || 0;

            // 计算剩余配额，最少为 0
            const photos_left = Math.max(0, regular_photos_limit - regular_photos_used);
            const subscriptionExpiryDate = user.subscription_expiry_date ?
                moment(user.subscription_expiry_date).tz("Asia/Shanghai").valueOf() :
                null;
            // last_usage_reset_date 用最后订阅过期时间
            const lastUsageResetDate = moment(subscriptionExpiryDate)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");

            // next_usage_reset_date 用过期时间+1个月（给下一周期普通配额计算用）
            const nextResetDate = moment(subscriptionExpiryDate)
                .tz("Asia/Shanghai")
                .add(1, 'month')
                .format("YYYY-MM-DD HH:mm:ss");

            // 为什么更新 photos/used/left 是这样逻辑？
            //   1. photos: 一定要设置为普通用户最大配额，即config.regular_photos_limit。
            //   2. photos_used: 一定用 regular_photos_used（存普通模式下历史已用数）。
            //   3. photos_left:    = photos - photos_used，最少为 0。

            await mysqlQuery(
                `UPDATE transir_users SET
                        user_type = 'regular',
                        subscription_expiry_date = NULL,
                        feature_minutes         = ?,
                        feature_minutes_used    = 0,
                        feature_minutes_left    = ?,
                        photos                  = ?,
                        photos_used             = ?,
                        photos_left             = ?,
                        tokens                  = ?,
                        tokens_used             = 0,
                        tokens_left             = ?,
                        float_copy              = ?,
                        float_copy_used         = 0,
                        float_copy_left         = ?,
                        quick_text              = ?,
                        quick_text_used         = 0,
                        quick_text_left         = ?,
                        last_usage_reset_date   = ?,
                        next_usage_reset_date   = ?
                     WHERE user_id = ?`,
                [
                    feature_minutes,         // 普通精翻分钟配额
                    feature_minutes,         // 剩余精翻分钟（初始等于配额）
                    regular_photos_limit,    // 照片总配额（普通）
                    regular_photos_used,     // 普通已用照片数 (regular_photos_used 字段)
                    photos_left,             // 普通剩余照片数
                    tokens,                  // 普通 tokens 配额
                    tokens,                  // 剩余 tokens
                    float_copy,              // 普通悬浮复制配额
                    float_copy,              // 剩余悬浮复制
                    quick_text,              // 普通快捷文字配额
                    quick_text,              // 剩余快捷文字
                    lastUsageResetDate,
                    nextResetDate,
                    user.user_id
                ]
            );
            // 内存同步
            user.user_type = 'regular';
            user.photos = regular_photos_limit;
            user.photos_used = regular_photos_used;
            user.photos_left = photos_left;
        }

        // ***【订阅过期检查结束】***



        // 3. 提前格式化所有相关时间（带判空，避免moment遇到 null/undefined 报错）
        const fieldsToFormat = [
            "registration_time", "last_login_time", "usage_base_date",
            "last_usage_reset_date", "next_usage_reset_date", "created_at", "updated_at",
            "subscription_start_date", "subscription_expiry_date"
        ];
        for (const field of fieldsToFormat) {
            user[field] = user[field] ? moment(user[field]).tz("Asia/Shanghai").format(
                "YYYY-MM-DD HH:mm:ss") : null;
        }
        // ======【新增：momo_balance同步代码段】======

        // 查询所有未过期未用完的充值记录剩余币数，求和，确保余额准确
        const balSql = `
SELECT SUM(remain) AS real_balance
FROM transir_recharge_records
WHERE user_id=? AND status='unused' AND 
      (expire_time IS NULL OR expire_time > NOW())
`;
        const [balRes] = await mysqlQuery(balSql, [user_id]);
        const accurate_balance = balRes && balRes.real_balance ? Number(balRes.real_balance) : 0;

        // 若表缓存与实际不一致，则自动修正（确保前端拿到的余额权威真实）
        if (user.momo_balance !== accurate_balance) {
            await mysqlQuery('UPDATE transir_users SET momo_balance=? WHERE user_id=?', [accurate_balance,
                user_id
            ]);
            user.momo_balance = accurate_balance;
        }

        // ======【新增代码段结束】======

        // 4. 检查是否到资源重置时间
        // 4. 检查是否到资源重置时间（强制用时间戳比较更保险）
        let needReset = false;
        const nowTs = Date.now(); // 当前服务器时间戳（毫秒）
        let nextResetTs = null;
        let nextResetMoment = null;
        if (user.next_usage_reset_date) {
            nextResetMoment = moment.tz(user.next_usage_reset_date, "Asia/Shanghai");
            nextResetTs = nextResetMoment.valueOf();
        }

        if (nextResetTs && nowTs > nextResetTs) {
            needReset = true;
        }


        if (needReset) {
            // 5. 根据当前身份分配资源配额
            let configSql = "";
            let configType = "";
            if (user.user_type === 'regular') {
                // 普通用户模式
                configSql = `
                 SELECT 
                   regular_feature_minutes AS feature_minutes, 
                   regular_photos_limit AS photos_limit,
                   regular_tokens_limit AS tokens,
                   regular_float_copy_limit AS float_copy,
                   regular_quick_text_limit AS quick_text
                 FROM transir_system_configs 
                 LIMIT 1
               `;
                configType = "普通用户";
            } else {
                // 会员用户模式
                configSql = `
                  SELECT 
                    member_feature_minutes AS feature_minutes, 
                    member_photos_limit AS photos_limit,
                    member_tokens_limit AS tokens,
                    member_float_copy_limit AS float_copy,
                    member_quick_text_limit AS quick_text
                  FROM transir_system_configs 
                  LIMIT 1
                `;
                configType = "会员";
            }
            const [config] = await mysqlQuery(configSql, []);

            // 5.3 构造资源重置SQL，动态拼接覆盖所有可重置资源字段
            // key 为 transir_users 字段名，value 为分配后的初值
            const resetMap = {
                feature_minutes: config.feature_minutes || 0,
                feature_minutes_used: 0,
                feature_minutes_left: config.feature_minutes || 0,

                tokens: config.tokens || 0,
                tokens_used: 0,
                tokens_left: config.tokens || 0,

                float_copy: config.float_copy || 0,
                float_copy_used: 0,
                float_copy_left: config.float_copy || 0,

                quick_text: config.quick_text || 0,
                quick_text_used: 0,
                quick_text_left: config.quick_text || 0
            };

            // ==========【重点：处理普通用户photos不重置，仅动态调整photos_left】==========
            if (user.user_type === 'regular') {
                // 只根据 regular_photos_used 来判断还能剩几次
                const totalInitPhotos = config.photos_limit || 0; // 注册送3次，通常是3
                const usedPhotos = user.regular_photos_used || 0;
                const leftPhotos = Math.max(0, totalInitPhotos - usedPhotos);
                // 不重置 photos/photos_used
                resetMap.photos_left = leftPhotos; // 只动态更新photos_left
                // 其它不用加，photos/photos_used 保持数据库内现值
            } else {
                // 会员/其它类型，正常全部重置
                resetMap.photos = config.photos_limit || 0;
                resetMap.photos_used = 0;
                resetMap.photos_left = config.photos_limit || 0;
            }

            // 6. 计算重置后日期
            // 重要！last_usage_reset_date 一定用 计划的 next_reset_time，切勿用 now
            const lastResetDate = nextResetMoment.format("YYYY-MM-DD HH:mm:ss");
            const newNextResetDate = nextResetMoment.clone().add(1, 'month').format("YYYY-MM-DD HH:mm:ss");
            // 5.5 补充更新时间字段
            resetMap.last_usage_reset_date = lastResetDate;
            resetMap.next_usage_reset_date = newNextResetDate;

            // 5.6 拼接 UPDATE SQL
            // SQL 格式如 SET a = ?, b = ?, c = ?
            const updateFields = Object.keys(resetMap).map(field => `${field} = ?`).join(', ');
            const updateSql = `UPDATE transir_users SET ${updateFields} WHERE user_id = ?`;
            const updateParams = [...Object.values(resetMap), user_id];
            await mysqlQuery(updateSql, updateParams);

            // 5.7 刷新用户数据，确保给前端展示的是最新重置的
            const [freshUser] = await mysqlQuery(sql, [user_id]);
            for (const field of fieldsToFormat) {
                freshUser[field] = freshUser[field] ? moment(freshUser[field]).tz("Asia/Shanghai").format(
                    "YYYY-MM-DD HH:mm:ss") : null;
            }
            user = freshUser;
        }
        // 9. 返回数据
        res.send({
            code: 0,
            msg: 'success',
            data: user
        });
    } catch (e) {
        console.error("userInfo error:", e);
        res.status(500).json({
            code: 500,
            message: '服务器错误'
        });
    }
});
router.post('/test', async (req, res) => {
    const jwtToken = createJWT('com.trans.translationAiAndSubtitle');
    const consumptionUrl =
        `https://api.storekit-sandbox.itunes.apple.com/inApps/v1/transactions/2000000952197999`;
    const config = {
        headers: {
            Authorization: `Bearer ${jwtToken}`,
            'Content-Type': 'application/json',
        },
    };
    const response = await axios.get(consumptionUrl, config);
    console.log(response)
    console.log(response.data.signedTransactionInfo)
    const decoded = jwt.decode(response.data.signedTransactionInfo, {
        complete: true
    });
    console.log(decoded.payload)


})
//获取用户信息
// router.post('/userInfo', async (req, res) => {
//     const { user_id, bundleId } = req.body;

//     try {
//         // 1. 查库取用户信息
//         const sql = 'SELECT * FROM transir_users WHERE user_id = ? LIMIT 1';
//         const result = await mysqlQuery(sql, [user_id]);
//         if (result.length === 0) {
//             return res.status(404).json({ code: 404, message: '用户不存在' });
//         }
//         const user = result[0];
//         let newUserType = user.user_type;

//         if (user.original_transaction_id) {
//             try {
//                 // 注意切换生产/沙盒环境
//                 const jwtToken = createJWT(bundleId);
//                 const consumptionUrl = `https://api.storekit.itunes.apple.com/inApps/v1/subscriptions/${user.original_transaction_id}`;
//                 const config = {
//                     headers: {
//                         Authorization: `Bearer ${jwtToken}`,
//                         'Content-Type': 'application/json',
//                     },
//                 };

//                 // 拉取苹果最新订阅数据
//                 const response = await axios.get(consumptionUrl, config);
//                 if (
//                     !response.data.data ||
//                     !response.data.data[0] ||
//                     !response.data.data[0].lastTransactions ||
//                     !response.data.data[0].lastTransactions[0]
//                 ) {
//                     throw new Error('未找到有效的 transaction 数据');
//                 }

//                 const transaction = response.data.data[0].lastTransactions[0];

//                 // 解包和验签
//                 const decodedToken = jwt.decode(transaction.signedTransactionInfo, { complete: true });

//                 if (decodedToken && decodedToken.header && decodedToken.header.x5c) {
//                     const publicKey = getKeyFromX5C(decodedToken.header.x5c);

//                     // 解包验签 signedTransactionInfo
//                     const signedTransactionInfo = await jwtVerifyAsync(
//                         transaction.signedTransactionInfo,
//                         publicKey,
//                         { algorithms: ['ES256'] }
//                     );
//                     // 解包验签 signedRenewalInfo
//                     const signedRenewalInfo = await jwtVerifyAsync(
//                         transaction.signedRenewalInfo,
//                         publicKey,
//                         { algorithms: ['ES256'] }
//                     );

//                     // 1. 会员身份判定
//                     const now = Date.now();
//                     const expires = Number(signedTransactionInfo.expiresDate || 0);

//                     if (signedTransactionInfo.offerType) {
//                         newUserType = expires > now ? 'trial_member' : 'regular';
//                     } else {
//                         newUserType = expires > now ? 'renewal_member' : 'regular';
//                     }

//                     // 2. 需要存入的字段，全部北京时间字符串
//                     const subscription_start_date = toBeijingStr(signedTransactionInfo.purchaseDate);
//                     const subscription_expiry_date = toBeijingStr(signedTransactionInfo.expiresDate);
//                     const usage_base_date = toBeijingStr(signedTransactionInfo.purchaseDate);  // 你的表设计如此
//                     const current_product_id = signedTransactionInfo.productId || null;
//                     const transaction_id = signedTransactionInfo.transactionId || null;

//                     // 3. 一次性落库所有新值
//                     await mysqlQuery(
//                         `UPDATE transir_users SET
//                             user_type=?,
//                             subscription_start_date=?,
//                             subscription_expiry_date=?,
//                             usage_base_date=?,
//                             current_product_id=?,
//                             transaction_id=?
//                         WHERE user_id=?`,
//                         [
//                             newUserType,
//                             subscription_start_date,
//                             subscription_expiry_date,
//                             usage_base_date,
//                             current_product_id,
//                             transaction_id,
//                             user_id
//                         ]
//                     );

//                     // 4. 返回给前端的 user 结构也记得同步最新值
//                     user.user_type = newUserType;
//                     user.subscription_start_date = subscription_start_date;
//                     user.subscription_expiry_date = subscription_expiry_date;
//                     user.usage_base_date = usage_base_date;
//                     user.current_product_id = current_product_id;
//                     user.transaction_id = transaction_id;
//                 } else {
//                     return res.status(501).json({ code: 501, message: '不支持该 JWT 格式，无x5c：兼容性需升级' });
//                 }
//             } catch (error) {
//                 console.error('苹果订阅查询或jwt验签失败:', error.response?.data || error.message || error);
//                 // 不阻断主流程，仅日志
//             }
//         }

//         // 返回用户信息（user_type已及时同步）
//         res.send({
//             code: 0,
//             msg: 'success',
//             data: user
//         });

//     } catch (e) {
//         res.status(500).json({ code: 500, message: '服务器错误' });
//     }
// });


//更新用户订阅信息
router.post('/updateSubscription', async (req, res) => {
    const {
        user_id,
        body
    } = req.body
    // 解包body，字段含义见你的代码注释
    const {
        productId, // 商品id
        transactionId, // 当前订单id
        originalTransactionId, // 原始订单id
        purchaseDate, // 当前订单购买时间（时间戳，毫秒）
        originalPurchaseDate, // 原始购买时间（时间戳，毫秒）
        subscriptionExpirationDate, // 订阅过期时间（时间戳，毫秒）
        cancellationDate, // 取消时间（时间戳，毫秒/可null）
        isTrialPeriod, // 是否试用，true/false 或 'true'/'false'
        isInIntroOfferPeriod // 是否优惠，true/false 或 'true'/'false'
    } = body;
    try {
        // 查询用户当前信息，用于判断用户类型是否变更和检查资源重置时间
        const currentUserResult = await mysqlQuery('SELECT * FROM transir_users WHERE user_id = ?', [
            user_id
        ]);
        console.log(currentUserResult)
        if (!currentUserResult || currentUserResult.length === 0) {
            return res.status(404).json({
                code: 404,
                message: '用户不存在'
            });
        }
        const currentUser = currentUserResult[0];
        const currentUserType = currentUser.user_type;

        // 1. 会员身份判定
        let newUserType = 'regular';
        const now = Date.now();
        const expires = Number(subscriptionExpirationDate || 0);

        // 只要在试用期且未过期，就是trial_member
        if (isTrialPeriod === true || isTrialPeriod === 'true') {
            newUserType = expires > now ? 'trial_member' : 'regular';
        } else {
            newUserType = expires > now ? 'renewal_member' : 'regular';
        }

        // 2. 字段全部转北京时间字符串
        function toBeijingStr(ts) {
            if (!ts) return null;
            return moment(Number(ts)).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
        }

        // 订阅相关字段（允许为空）
        let subscription_start_date = toBeijingStr(purchaseDate);
        const subscription_expiry_date = toBeijingStr(subscriptionExpirationDate);
        // 初始设置usage_base_date为当前值，后面可能会更新
        let usage_base_date = currentUser.usage_base_date || toBeijingStr(purchaseDate);
        const cancellation_date = toBeijingStr(cancellationDate);

        const current_product_id = productId || null;
        const transaction_id = transactionId || null;
        const original_transaction_id = originalTransactionId || null;

        // 获取系统配置
        const configQuery = await mysqlQuery(
            'SELECT regular_feature_minutes, regular_photos_limit, member_feature_minutes, member_photos_limit FROM transir_system_configs LIMIT 1'
        );
        if (!configQuery || configQuery.length === 0) {
            return res.status(500).json({
                code: 500,
                message: '系统配置未找到'
            });
        }
        const config = configQuery[0];

        // 3. 检查是否需要重置资源使用情况
        let shouldResetUsage = false;
        let feature_minutes, photos;
        let nextResetDate = null;
        let lastResetDate = null;

        // 判断用户类型是否变更
        const userTypeChanged = currentUserType !== newUserType;

        // ******重要：根据用户类型分配资源配额******
        // 根据用户最新类型设置相应的资源配额
        if (newUserType === 'regular') {
            // 普通用户配额
            feature_minutes = config.regular_feature_minutes;
            photos = config.regular_photos_limit;
            console.log(`用户 ${user_id} 为普通用户，分配普通配额: ${feature_minutes}分钟/${photos}张照片`);
        } else {
            // 会员用户配额 (trial_member, renewal_member, temporary_member)
            feature_minutes = config.member_feature_minutes;
            photos = config.member_photos_limit;
            console.log(`用户 ${user_id} 为会员用户(${newUserType})，分配会员配额: ${feature_minutes}分钟/${photos}张照片`);
        }

        // 当前时间（北京时间）
        const currentDateTime = moment().tz("Asia/Shanghai");
        const currentTimeStr = currentDateTime.format("YYYY-MM-DD HH:mm:ss");

        // 用户类型变更导致资源重置
        if (userTypeChanged) {
            shouldResetUsage = true;

            // 用户类型变更时，更新关键日期
            lastResetDate = currentTimeStr;

            // 重要修改：当用户类型变更时，更新usage_base_date为当前购买时间或当前时间
            // 优先使用购买时间，如果没有则使用当前时间
            usage_base_date = subscription_start_date || currentTimeStr;

            console.log(`用户 ${user_id} 会员类型从 ${currentUserType} 变更为 ${newUserType}，需要重置资源用量及更新基准日期`);
            console.log(`更新后的基准日期: ${usage_base_date}, 上次重置时间: ${lastResetDate}`);
        }

        // 检查是否到了资源重置时间
        const nextResetDateTime = currentUser.next_usage_reset_date ?
            moment(currentUser.next_usage_reset_date).tz("Asia/Shanghai") :
            null;

        // 如果已有下次重置时间并且当前时间已过重置时间，则需要重置
        if (nextResetDateTime && currentDateTime.isAfter(nextResetDateTime)) {
            shouldResetUsage = true;
            // 重要修改：使用计划的重置时间而不是当前时间作为上次重置时间
            // 这样即使用户10天后才登录，重置日期仍然是正确的
            lastResetDate = nextResetDateTime.format("YYYY-MM-DD HH:mm:ss");
            console.log(`用户 ${user_id} 已到资源重置时间，需要重置资源用量，上次重置时间设为原计划重置时间: ${lastResetDate}`);
        }

        // 计算下一次重置时间
        // 根据上次重置时间计算下次重置时间
        let baseResetDate;

        if (shouldResetUsage && lastResetDate) {
            // 如果需要重置，使用新确定的上次重置时间作为基准
            baseResetDate = moment(lastResetDate).tz("Asia/Shanghai");
        } else if (currentUser.last_usage_reset_date) {
            // 否则使用现有的上次重置时间作为基准
            baseResetDate = moment(currentUser.last_usage_reset_date).tz("Asia/Shanghai");
        } else {
            // 首次设置，使用当前时间作为基准
            baseResetDate = currentDateTime;
            // 首次设置，同时更新上次重置时间
            lastResetDate = currentTimeStr;
        }

        // 复制一份基准时间进行计算，避免修改原始对象
        let calculatedNextReset = moment(baseResetDate);

        // 增加一个月，处理月底日期问题
        calculatedNextReset.add(1, 'month');

        // 格式化为数据库可接受的格式
        nextResetDate = calculatedNextReset.format("YYYY-MM-DD HH:mm:ss");

        console.log(
            `用户 ${user_id} 日期更新情况: usage_base_date=${usage_base_date}, last_reset=${lastResetDate}, next_reset=${nextResetDate}`
        );

        // 执行数据库更新
        let updateFields = {};
        let updateParams = [];
        let updateSQL = 'UPDATE transir_users SET ';

        // 如果需要重置用量
        if (shouldResetUsage) {
            // 重置资源用量，根据用户类型设置新的配额
            updateFields = {
                user_type: newUserType,
                subscription_start_date: subscription_start_date,
                subscription_expiry_date: subscription_expiry_date,
                usage_base_date: usage_base_date, // 使用可能已更新的usage_base_date
                current_product_id: current_product_id,
                transaction_id: transaction_id,
                original_transaction_id: original_transaction_id,
                // 根据最新用户类型设置资源配额
                feature_minutes: feature_minutes,
                photos: photos,
                feature_minutes_used: 0, // 重置已使用量
                photos_used: 0, // 重置已使用量
                feature_minutes_left: feature_minutes, // 更新剩余量
                photos_left: photos, // 更新剩余量
                last_usage_reset_date: lastResetDate, // 使用计算的上次重置时间
                next_usage_reset_date: nextResetDate // 设置下次重置时间
            };

            console.log(
                `用户 ${user_id} 重置资源用量: feature_minutes=${feature_minutes}, photos=${photos}, last_reset=${lastResetDate}, next_reset=${nextResetDate}`
            );
        } else {
            // 仅更新订阅相关信息，不重置用量
            updateFields = {
                user_type: newUserType,
                subscription_start_date: subscription_start_date,
                subscription_expiry_date: subscription_expiry_date,
                usage_base_date: usage_base_date, // 使用可能已更新的usage_base_date
                current_product_id: current_product_id,
                transaction_id: transaction_id,
                original_transaction_id: original_transaction_id
            };

            // 如果这是用户首次设置reset日期
            if (!currentUser.next_usage_reset_date) {
                updateFields.last_usage_reset_date = lastResetDate;
                updateFields.next_usage_reset_date = nextResetDate;
            }

            console.log(`用户 ${user_id} 无需重置资源用量，保持原有配额`);
        }

        // 构建SQL语句
        const updateEntries = Object.entries(updateFields).filter(([_, value]) => value !== undefined);
        updateSQL += updateEntries.map(([key, _]) => `${key} = ?`).join(', ');
        updateSQL += ' WHERE user_id = ?';

        // 准备参数
        updateParams = [...updateEntries.map(([_, value]) => value), user_id];

        // 执行更新
        await mysqlQuery(updateSQL, updateParams);

        // 查询并返回最新用户数据
        const users = await mysqlQuery('SELECT * FROM transir_users WHERE user_id = ?', [user_id]);
        const user = users[0];

        // 格式化返回的时间：防止Date对象自动变成ISO
        [
            "subscription_start_date", "subscription_expiry_date",
            "usage_base_date", "registration_time", "last_login_time",
            "last_usage_reset_date", "next_usage_reset_date", "cancellation_date"
        ].forEach(field => {
            if (user[field]) {
                user[field] = moment(user[field]).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
            }
        });

        res.json({
            code: 0,
            msg: 'success',
            data: user
        });

    } catch (e) {
        console.error('updateSubscription error:', e);
        res.status(500).json({
            code: 500,
            message: '服务器错误'
        });
    }
});
// router.post('/jjjj', async (req, res) => {
//     const {
//         user_id
//     } = req.body;
//     const userRes = await mysqlQuery('SELECT * FROM transir_users WHERE user_id = ?', [user_id]);
//     const user = userRes[0]; // 取得这条用户记录
//     console.log(new Date(user.subscription_expiry_date).getTime(),'subscription_expiry_date')
//     console.log(user)
// })

// 用户主动在客户端购买会员时调用（不限首次，也不限上次是否因取消掉过）
router.post('/subscription/activateByClient', async (req, res) => {
    const chinaTime = new Date();

    // 解构参数
    const {
        user_id,
        body
    } = req.body;

    // 1. 基础参数校验
    if (!user_id || !body || typeof body !== 'object') {
        return res.status(400).json({
            code: 400,
            message: '参数错误：user_id 和 body 必须提供'
        });
    }

    // 2. body 中的订阅相关参数解构
    const {
        productId, // 商品ID
        transactionId, // 当前订单ID
        originalTransactionId, // 原始订单ID
        purchaseDate, // 当前订单购买时间（时间戳，毫秒）
        subscriptionExpirationDate, // 订阅过期时间（时间戳，毫秒）
        isTrialPeriod // 是否试用
    } = body;

    try {
        // 3. 查找用户信息
        const userRes = await mysqlQuery('SELECT * FROM transir_users WHERE user_id = ?', [user_id]);
        if (!userRes || userRes.length === 0) {
            return res.status(404).json({
                code: 404,
                message: '用户不存在'
            });
        }

        console.log(body, 'body-----' + chinaTime.toLocaleString())
        // =============== 新增逻辑：originalTransactionId 全局唯一性校验 =================
        // 查找有没有其他账号（不是当前user_id）在用同一个originalTransactionId
        if (originalTransactionId) {
            const existRes = await mysqlQuery(
                'SELECT user_id, username FROM transir_users WHERE original_transaction_id = ? AND user_id != ? LIMIT 1',
                [originalTransactionId, user_id]
            );
            if (existRes.length > 0) {
                // 查到已被别的账号绑定该订阅
                return res.send({
                    code: 1001,
                    message: '该订阅已被其他账号绑定',
                    data: {
                        using_user_id: existRes[0].user_id,
                        using_username: existRes[0].username
                    }
                });
            }
        }
        // =================== ↑↑↑↑↑↑ 这是你要求的唯一性校验位置  ↑↑↑↑↑↑=================
        const user = userRes[0]; // 取得这条用户记录

        // -- 新增判断【避免重复续费/重新激活】 --
        // 数据库里本地过期时间
        const localExpiry = user.subscription_expiry_date ? new Date(user.subscription_expiry_date).getTime() : 0;
        // 客户端传的过期时间
        const clientExpiry = Number(subscriptionExpirationDate || 0);

        // 如果当前是会员，且本地过期时间比客户端时间要晚，则直接返回成功。防止重复激活。
        if (
            user.user_type === 'renewal_member' &&
            localExpiry > 0 && clientExpiry > 0 &&
            localExpiry > clientExpiry
        ) {
            // 直接返回，不再进行后续初始化/激活操作
            return res.send({
                code: 0,
                msg: '会员激活/购买成功',
                data: user
            });
        }

        // 5. 会员类型判定
        let newUserType;
        let now = Date.now();
        const expires = Number(subscriptionExpirationDate || 0);

        if (isTrialPeriod === true || isTrialPeriod === 'true') {
            newUserType = expires > now ? 'trial_member' : 'regular';
        } else {
            newUserType = expires > now ? 'renewal_member' : 'regular';
        }

        // 6. 时间戳转北京时间字符串
        function toBeijingStr(ts) {
            if (!ts) return null;
            return moment(Number(ts)).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
        }
        const subscription_start_date = toBeijingStr(purchaseDate);
        const subscription_expiry_date = toBeijingStr(subscriptionExpirationDate);
        let nows = toBeijingStr(now);
        console.log(subscription_expiry_date, '过期时间----' + chinaTime.toLocaleString())
        console.log(nows, '当前时间----' + chinaTime.toLocaleString())
        const usage_base_date = subscription_start_date; // 本次激活的基准，就是本次购买时间

        // 7. 读取会员配额
        const configQuery = await mysqlQuery(
            `SELECT
                member_feature_minutes,
                member_photos_limit,
                member_tokens_limit,
                member_float_copy_limit,
                member_quick_text_limit
            FROM transir_system_configs
            LIMIT 1`
        );
        if (!configQuery || configQuery.length === 0) {
            return res.status(500).json({
                code: 500,
                message: '系统参数缺失'
            });
        }
        const config = configQuery[0];
        const feature_minutes = config.member_feature_minutes;
        const photos = config.member_photos_limit;

        // 8. 流量统计周期重置时间
        // const currentTimeStr = subscription_start_date;
        // const nextResetDate = moment(usage_base_date).tz("Asia/Shanghai").add(1, 'month').format("YYYY-MM-DD HH:mm:ss");
        // 8. 流量统计周期重置时间
        const currentTimeStr = subscription_start_date;

        let nextResetDate;
        // 如果是体验会员，重置日期就是trial到期时间
        if (isTrialPeriod === true || isTrialPeriod === 'true') {
            // subscription_expiry_date 已经是北京时间格式
            nextResetDate = subscription_expiry_date;
        } else {
            // 常规会员一个月后
            nextResetDate = moment(usage_base_date).tz("Asia/Shanghai").add(1, 'month').format(
                "YYYY-MM-DD HH:mm:ss");
        }
        // 9. 资源字段初始化，这里每个资源都会初始化：总量、本周期已用、剩余
        const resetResources = {
            feature_minutes: config.member_feature_minutes || 0,
            feature_minutes_used: 0,
            feature_minutes_left: config.member_feature_minutes || 0,

            photos: config.member_photos_limit || 0,
            photos_used: 0,
            photos_left: config.member_photos_limit || 0,

            tokens: config.member_tokens_limit || 0,
            tokens_used: 0,
            tokens_left: config.member_tokens_limit || 0,

            float_copy: config.member_float_copy_limit || 0,
            float_copy_used: 0,
            float_copy_left: config.member_float_copy_limit || 0,

            quick_text: config.member_quick_text_limit || 0,
            quick_text_used: 0,
            quick_text_left: config.member_quick_text_limit || 0
        };
        // 10. UPDATE语句生成，保证所有资源字段一次性写入。后续如有扩展，只需加 resetResources 字段即可
        const updateFields = [
            'user_type', 'subscription_start_date', 'subscription_expiry_date', 'usage_base_date',
            'current_product_id', 'transaction_id', 'original_transaction_id',
            'last_usage_reset_date', 'next_usage_reset_date'
        ].concat(Object.keys(resetResources)); // 必要＆资源字段全部拼接
        const updateValues = [
            newUserType,
            subscription_start_date,
            subscription_expiry_date,
            usage_base_date,
            productId || null,
            transactionId || null,
            originalTransactionId || null,
            subscription_start_date, // last_usage_reset_date 初始化为激活时间
            nextResetDate
        ].concat(Object.values(resetResources));


        // 根据字段动态生成SQL：SET a=?, b=?, ...
        const updateSql = `
            UPDATE transir_users SET 
                ${updateFields.map(field => `${field}=?`).join(', ')}
            WHERE user_id = ?`;

        await mysqlQuery(updateSql, [...updateValues, user_id]);

        // 11. 查询并格式化最新用户数据
        const users = await mysqlQuery('SELECT * FROM transir_users WHERE user_id = ?', [user_id]);
        const updatedUser = users[0];
        [
            "subscription_start_date", "subscription_expiry_date",
            "usage_base_date", "registration_time", "last_login_time",
            "last_usage_reset_date", "next_usage_reset_date", "cancellation_date"
        ].forEach(field => {
            if (updatedUser[field]) {
                updatedUser[field] = moment(updatedUser[field]).tz("Asia/Shanghai").format(
                    "YYYY-MM-DD HH:mm:ss");
            }
        });

        return res.json({
            code: 0,
            msg: '会员激活/购买成功',
            data: updatedUser
        })


    } catch (e) {
        console.error('activateByClient error:', e);
        res.status(500).json({
            code: 500,
            message: '服务器错误'
        });
    }
});



//更新用户计时用量
// router.post('/updateFeatureUsage', async (req, res) => {
//     try {
//         // 1. 获取请求参数
//         const { user_id, feature_minutes_used, photos_used } = req.body;

//         // 2. 校验用户ID
//         if (!user_id) {
//             return res.status(400).json({ code: 400, message: '缺少user_id参数' });
//         }

//         // 3. 查询用户
//         const sql = 'SELECT * FROM transir_users WHERE user_id = ? LIMIT 1';
//         const result = await mysqlQuery(sql, [user_id]);
//         if (result.length === 0) {
//             return res.status(404).json({ code: 404, message: '用户不存在' });
//         }
//         const user = result[0];

//         // 4. 取数据库当前累计已用量，没有就用0
//         const oldFeatureMinutesUsed = user.feature_minutes_used || 0;
//         const oldPhotosUsed = user.photos_used || 0;

//         // 5. 取本次新增用量，没有就用0
//         const currentFeatureMinutesUsed = (feature_minutes_used !== undefined && !isNaN(Number(feature_minutes_used)))
//             ? Number(feature_minutes_used)
//             : 0;
//         const currentPhotosUsed = (photos_used !== undefined && !isNaN(Number(photos_used)))
//             ? Number(photos_used)
//             : 0;
//         console.log('本次新增用量---', currentFeatureMinutesUsed)
//         // 6. 累加为新的累计用量
//         const newFeatureMinutesUsed = oldFeatureMinutesUsed + currentFeatureMinutesUsed;
//         console.log('累加为新的累计用量---', newFeatureMinutesUsed)

//         const newPhotosUsed = oldPhotosUsed + currentPhotosUsed;

//         // 7. 获取总量（如果历史为null用0）
//         const featureMinutes = user.feature_minutes || 0;
//         const photos = user.photos || 0;

//         // 8. 计算剩余，最小不能为负数
//         const featureMinutesLeft = Math.max(0, featureMinutes - newFeatureMinutesUsed);
//         console.log('计算剩余---', featureMinutesLeft)

//         const photosLeft = Math.max(0, photos - newPhotosUsed);

//         // 9. 更新数据库
//         const updateSql = `
//             UPDATE transir_users
//             SET feature_minutes_used = ?, photos_used = ?, feature_minutes_left = ?, photos_left = ?, updated_at = NOW()
//             WHERE user_id = ?
//         `;
//         await mysqlQuery(updateSql, [
//             newFeatureMinutesUsed,
//             newPhotosUsed,
//             featureMinutesLeft,
//             photosLeft,
//             user_id
//         ]);

//         // 10. 查询最新数据
//         const latestUserArr = await mysqlQuery('SELECT * FROM transir_users WHERE user_id = ?', [user_id]);
//         const latestUser = latestUserArr[0];

//         // 11. 格式化所有时间字段
//         const dateFields = [
//             'registration_time', 'last_login_time',
//             'usage_base_date', 'last_usage_reset_date', 'next_usage_reset_date',
//             'created_at', 'updated_at'
//         ];
//         dateFields.forEach(field => {
//             latestUser[field] = latestUser[field]
//                 ? moment(latestUser[field]).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss")
//                 : null;
//         });

//         // 12. 返回结果
//         res.json({
//             code: 0,
//             msg: 'success',
//             data: latestUser
//         });

//     } catch (e) {
//         console.error(e);
//         res.status(500).json({ code: 500, message: '服务器错误' });
//     }
// });

// 更新用户各类资源用量（支持 feature_minutes, photos, tokens, float_copy, quick_text）
router.post('/updateFeatureUsage', async (req, res) => {
    try {
        // 1. 结构赋值获取所有参数
        const {
            user_id,
            feature_minutes_used,
            photos_used,
            tokens_used,
            float_copy_used,
            quick_text_used
        } = req.body;
        // 2. 校验user_id
        if (!user_id) {
            return res.status(400).json({
                code: 400,
                message: '缺少user_id参数'
            });
        }

        // 3. 查询用户
        const sql = 'SELECT * FROM transir_users WHERE user_id = ? LIMIT 1';
        const result = await mysqlQuery(sql, [user_id]);
        if (result.length === 0) {
            return res.status(404).json({
                code: 404,
                message: '用户不存在'
            });
        }
        const user = result[0];

        // 4. 需要被支持的资源字段（以后有新字段只需在这里加即可）
        const fields = [{
            base: 'feature_minutes',
            used: feature_minutes_used
        },
        {
            base: 'photos',
            used: photos_used
        },
        {
            base: 'tokens',
            used: tokens_used
        },
        {
            base: 'float_copy',
            used: float_copy_used
        },
        {
            base: 'quick_text',
            used: quick_text_used
        }
        ];
        // 5. 构建要更新的字段和值
        // 每个资源类型都自动处理 used/left 累加与计算
        let updateFragments = [];
        let updateValues = [];
        let anyFieldChanged = false;

        for (const field of fields) {
            // 数据有效才处理
            if (field.used !== undefined && !isNaN(Number(field.used))) {
                anyFieldChanged = true;
                const usedField = `${field.base}_used`;
                const leftField = `${field.base}_left`;

                // 原有已用量、总量
                const oldUsed = user[usedField] || 0;
                const totalVal = user[field.base] || 0;
                // 新累计已用
                const newUsed = oldUsed + Number(field.used);
                // 新剩余，不得为负
                const newLeft = Math.max(0, totalVal - newUsed);

                // SQL更新片段和值
                updateFragments.push(`${usedField}=?`, `${leftField}=?`);
                updateValues.push(newUsed, newLeft);
            }
        }

        if (!anyFieldChanged) {
            return res.status(400).json({
                code: 400,
                message: '没有需要更新的资源用量参数'
            });
        }

        // 6. 补充 updated_at 字段
        updateFragments.push('updated_at = NOW()');

        // 7. 执行更新
        const updateSql = `
            UPDATE transir_users
            SET ${updateFragments.join(', ')}
            WHERE user_id = ?
        `;
        updateValues.push(user_id);
        await mysqlQuery(updateSql, updateValues);

        // 8. 查询最新用户信息
        const latestUserArr = await mysqlQuery('SELECT * FROM transir_users WHERE user_id = ?', [user_id]);
        const latestUser = latestUserArr[0];

        // 9. 格式化所有时间字段
        const dateFields = [
            'registration_time', 'last_login_time',
            'usage_base_date', 'last_usage_reset_date', 'next_usage_reset_date',
            'created_at', 'updated_at'
        ];
        dateFields.forEach(field => {
            latestUser[field] = latestUser[field] ?
                moment(latestUser[field]).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss") :
                null;
        });

        // 10. 返回成功
        res.json({
            code: 0,
            msg: 'success',
            data: latestUser
        });
    } catch (e) {
        console.error(e);
        res.status(500).json({
            code: 500,
            message: '服务器错误'
        });
    }
});



//更新配置表普通/会员用户的流量
// 更新系统配置：支持表内所有字段，部分字段未传则不更新
router.post('/updateConfig', async (req, res) => {
    // 1. 定义当前允许配置的字段列表
    const fields = [
        "regular_feature_minutes",
        "member_feature_minutes",
        "regular_photos_limit",
        "member_photos_limit",
        "regular_tokens_limit",
        "member_tokens_limit",
        "regular_float_copy_limit",
        "member_float_copy_limit",
        "regular_quick_text_limit",
        "member_quick_text_limit",
        "redeem_tokens_per_10_momo",
        "web_capture_per_piece",
        "float_copy_per_time",
        "quick_capture_per_piece",
        "quick_text_per_time",
        "accurate_minute_per_time",
        "quick_translate_minute_per_time",
        "comic_realtime_minute_per_time",
        "enable_guide_page"
    ];

    // 2. 动态拼接SQL，仅更新实际传递的字段
    let updateSet = [];
    let updateVals = [];
    for (let f of fields) {
        if (req.body.hasOwnProperty(f)) {
            updateSet.push(`${f} = ?`);
            updateVals.push(req.body[f]);
        }
    }

    if (updateSet.length === 0) {
        return res.status(400).json({
            code: 400,
            msg: "没有需要更新的字段"
        });
    }

    // 温馨说明：只有一行
    const updateSql = `UPDATE transir_system_configs SET ${updateSet.join(', ')} LIMIT 1`;
    try {
        await mysqlQuery(updateSql, updateVals);

        // 查询并返回最新配置
        const latestConfigArr = await mysqlQuery('SELECT * FROM transir_system_configs LIMIT 1');
        const latestConfig = latestConfigArr[0];

        res.json({
            code: 0,
            msg: '更新成功',
            data: latestConfig
        });

    } catch (e) {
        console.error('系统配置更新失败:', e);
        res.status(500).json({
            code: 500,
            message: '服务器错误'
        });
    }
});


//获取用户列表
router.post('/getUserList', async (req, res) => {
    let {
        page,
        size
    } = utils.pageSize(req.body.page, req.body.size);
    let {
        username,
        dateRange,
        user_type,
        momo_balance_min,
        invited_count_min,
        user_demand,           // 新增：用户兴趣标签，数组 or 字符串
        user_source_channel,   // 新增：渠道数组 or 字符串
        country,               // 新增
        feature_used           // 新增
    } = req.body;
    let sqlConditions = [];
    const sqlParams = [];
    if (username) {
        sqlConditions.push("username = ?");
        sqlParams.push(username);
    }
    if (user_type) {
        sqlConditions.push("user_type = ?");
        sqlParams.push(user_type);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sqlConditions.push("created_at BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }

    if (user_demand.length) {
        sqlConditions.push('JSON_CONTAINS(user_demand, ?)');
        sqlParams.push(JSON.stringify(user_demand));
    }

    if (user_source_channel.length) {

        sqlConditions.push('JSON_CONTAINS(user_source_channel, ?)');
        sqlParams.push(JSON.stringify(user_source_channel));
    }

    // 国家
    if (country) {
        sqlConditions.push("country = ?");
        sqlParams.push(country);
    }

    // 是否使用过（0/1）
    if (feature_used !== undefined && feature_used !== '' && feature_used !== null) {
        sqlConditions.push("feature_used = ?");
        sqlParams.push(feature_used);
    }

    // 2. 新增：过滤apple_identifier长度小于10
    sqlConditions.push("LENGTH(apple_identifier) >= 10");

    // 3. 新增：过滤几个特定apple_identifier
    const appleIdsToExclude = [
        '000672.0e4740c4414441b2be8fba92997a789e.0324',
        '000672.b8b722bc0df24a4d95afa7aeadc340f0.1029',
        '000448.ab3b7bfdc4c245daba6a0de0948e5a1b.0418'
    ];
    if (appleIdsToExclude.length > 0) {
        // 允许apple_identifier为NULL不要过滤，但只要不为null且在排除列表就要去掉
        let excludePlaceholders = appleIdsToExclude.map(() => "?").join(", ");
        sqlConditions.push(`(apple_identifier IS NULL OR apple_identifier NOT IN (${excludePlaceholders}))`);
        sqlParams.push(...appleIdsToExclude);
    }
    // 添加条件：确保 message 字段有值
    // 【新加】momo币数量最小值筛选
    // ---- 新增1：momo币最小值大于等于 ----
    if (typeof momo_balance_min !== 'undefined' && momo_balance_min !== null && momo_balance_min !== '') {
        sqlConditions.push("momo_balance >= ?");
        sqlParams.push(Number(momo_balance_min));
    }

    // ---- 新增2：邀请人数最小值大于等于 ----
    if (typeof invited_count_min !== 'undefined' && invited_count_min !== null && invited_count_min !== '') {
        sqlConditions.push("invited_count >= ?");
        sqlParams.push(Number(invited_count_min));
    }
    let sqlBase = `SELECT * FROM transir_users`;
    let totalBase = "FROM transir_users";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let {
        total
    } = await utils.getListSumMOMO(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY created_at DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    await poolmomo.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({
            code: -1,
            err,
            req
        }));
        result.forEach((element) => {
            function fmt(val) {
                return val ? moment(val).tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss") :
                    null;
            }
            element.created_at = fmt(element.created_at);
            element.registration_time = fmt(element.registration_time);
            element.last_login_time = fmt(element.last_login_time);
            element.subscription_start_date = fmt(element.subscription_start_date);
            element.subscription_expiry_date = fmt(element.subscription_expiry_date);
            element.usage_base_date = fmt(element.usage_base_date);
            element.last_usage_reset_date = fmt(element.last_usage_reset_date);
            element.next_usage_reset_date = fmt(element.next_usage_reset_date);
            element.last_active_time = fmt(element.last_active_time);

            function formatDuration(sec) {
                sec = Math.floor(sec);
                if (isNaN(sec) || sec < 0) return '-';
                if (sec < 60) return `${sec}秒`;
                const min = Math.floor(sec / 60);
                const remainSec = sec % 60;
                return remainSec === 0 ?
                    `${min}分钟` :
                    `${min}分${remainSec}秒`;
            }
            element.feature_minutes = formatDuration(element.feature_minutes);
            element.feature_minutes_used = formatDuration(element.feature_minutes_used);
            element.feature_minutes_left = formatDuration(element.feature_minutes_left);
            // ------------ 新增：离线天数计算 ------------
            // 获取 Asia/Shanghai 当前0点
            const today = moment().tz("Asia/Shanghai").startOf('day');
            let lastActive = element.last_active_time ?
                moment(element.last_active_time, "YYYY-MM-DD HH:mm:ss").startOf('day') :
                null;

            if (lastActive && lastActive.isValid()) {
                let diffDays = today.diff(lastActive, 'days');
                element.last_offline_days = diffDays;
            } else {
                // 无活跃时间统一返回 -1 或空字符串，看你业务需求
                element.last_offline_days = '-';
            }
            // ------------ 新增结束 ------------
        });

        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
})

router.post('/countryList', async (req, res) => {
    try {
        const sql = `SELECT DISTINCT country 
                     FROM transir_users
                     WHERE country IS NOT NULL AND country != ''
                     ORDER BY country`;
        const result = await new Promise((resolve, reject) => {
            poolmomo.query(sql, [], (err, rows) => {
                if (err) return reject(err);
                resolve(rows);
            });
        });
        // 只返回扁平数组
        const countryList = result.map(r => r.country);
        res.json({ code: 0, data: countryList, msg: '获取成功' });
    } catch (e) {
        res.status(500).json({ code: -1, msg: '数据库查询失败', error: e.message });
    }
});

router.get('/getJsVersions', async (req, res) => {
    let sql = `SELECT * FROM transir_js_script_versions_conf LIMIT 1`
    let result = await mysqlQuery(sql, [])
    res.send({
        code: 0,
        msg: 'success',
        data: result
    })
})

router.get('/getSubtitleJS', (req, res) => {
    // 构造文件路径
    const filePath = path.join('/TransirJs', `subtitle.zip`);
    // 检查文件是否存在
    fs.access(filePath, fs.constants.R_OK, (err) => {
        if (err) {
            return res.status(404).send("文件未找到。");
        }

        // 设置下载响应头
        res.setHeader('Content-Disposition', `attachment; filename="subtitle.zip"`);
        res.setHeader('Content-Type', 'application/zip');

        // 使用文件流发送文件
        const fileStream = fs.createReadStream(filePath);
        fileStream.pipe(res);
    });
});
router.get('/getTranslateJS', (req, res) => {
    // 构造文件路径
    const filePath = path.join('/TransirJs', `translate.zip`);
    // 检查文件是否存在
    fs.access(filePath, fs.constants.R_OK, (err) => {
        if (err) {
            return res.status(404).send("文件未找到。");
        }

        // 设置下载响应头
        res.setHeader('Content-Disposition', `attachment; filename="translate.zip"`);
        res.setHeader('Content-Type', 'application/zip');

        // 使用文件流发送文件
        const fileStream = fs.createReadStream(filePath);
        fileStream.pipe(res);
    });
});



// async function mysqlQuery(sql, arr) {

//     return new Promise((resolve, reject) => {
//         poolmomo.query(sql, arr, (error, results, fields) => {
//             if (error) {
//                 return reject(error);
//             }
//             resolve(results);
//         });
//     });
// }
async function mysqlQuery(sql, params) {
    return new Promise((resolve, reject) => {
        poolmomo.getConnection((err, conn) => {
            if (err) return reject(err);
            conn.query(sql, params, (err, results) => {
                conn.release();
                if (err) return reject(err);
                resolve(results);
            });
        });
    });
}
// 创建 JWT
function createJWT(pk) {
    let privateKey = null
    let keyId = null
    let teamId = null
    if (pk == 'com.seamobitech.roku') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/iosRoku.p8');
        keyId = 'KPTK8YW54N';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.UniversalTVRemoteControl') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/universal.p8');
        keyId = 'NDVH93RZ93';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.whatsscanwebclone') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/waai.p8');
        keyId = 'M9HSW38PQ7';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.QRCodeReadAndGenerate') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/qr.p8');
        keyId = 'BPK84L5D9P';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.BeautyAI') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/beauty.p8');
        keyId = 'YKSHCJXDZ3';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.seamobitech.HeartPulse') {
        privateKey = fs.readFileSync('/zj-manager/zj-background-manager/iosHeartPulse.p8');
        keyId = '33R7CA35M3';
        teamId = '2beb4915-8807-44e7-a126-e3939cb6ff63';
    }
    if (pk == 'com.trans.translationAiAndSubtitle') {
        privateKey = fs.readFileSync('/home/wwwroot/default/iosTransir.p8');
        keyId = '47KPS4C6NW';
        teamId = '701e51de-a934-4e08-96e1-5513f30f0d04';
    }
    if (pk == 'com.TranslationAISubTitle') {
        privateKey = fs.readFileSync('/home/wwwroot/default/iosTransir-test.p8');
        keyId = 'N9PAX7T5T7';
        teamId = '5d6b7154-dee7-4d4e-8a81-77cbb774b30a';
    }
    // if (pk == 'com.pro.tv.remote.control.universal') {
    //     privateKey = fs.readFileSync('/home/wwwroot/default/AuthKey_NW88JX49D7.p8');
    //     keyId = 'NW88JX49D7';
    //     teamId = '6429b6cb-cac5-42b6-bed2-d0e72bc898e1';
    // }
    const now = Math.floor(Date.now() / 1000);
    const expiresIn = 60 * 60;

    const payload = {
        iss: teamId,
        iat: now,
        exp: now + expiresIn,
        aud: 'appstoreconnect-v1',
        bid: pk
    };

    const token = jwt.sign(payload, privateKey, {
        algorithm: 'ES256',
        keyid: keyId
    });

    return token;
}
// 从 x5c 中提取公钥
function getKeyFromX5C(x5c) {
    // 将第一个证书从 x5c 中取出并转换为 PEM 格式
    const pemCert = `-----BEGIN CERTIFICATE-----\n${x5c[0]}\n-----END CERTIFICATE-----`;
    // 从 PEM 证书生成公钥
    const publicKey = crypto.createPublicKey(pemCert).export({
        type: 'spki',
        format: 'pem'
    });
    return publicKey;
}
// 从 x5c 中提取Kid
function getKeyFromX5CKid(x5c) {
    // 将第一个证书从 x5c 中取出并转换为 PEM 格式
    const pemCert1 = `-----BEGIN CERTIFICATE-----\n${x5c[0]}\n-----END CERTIFICATE-----`;

    // 计算证书的 SHA-256 指纹
    const certHash1 = crypto.createHash('sha256');
    certHash1.update(pemCert1);
    const certFingerprint1 = certHash1.digest('hex');
    const pemCert2 = `-----BEGIN CERTIFICATE-----\n${x5c[1]}\n-----END CERTIFICATE-----`;

    // 计算证书的 SHA-256 指纹
    const certHash2 = crypto.createHash('sha256');
    certHash2.update(pemCert2);
    const certFingerprint2 = certHash2.digest('hex');
    const pemCert3 = `-----BEGIN CERTIFICATE-----\n${x5c[2]}\n-----END CERTIFICATE-----`;

    // 计算证书的 SHA-256 指纹
    const certHash3 = crypto.createHash('sha256');
    certHash3.update(pemCert3);
    const certFingerprint3 = certHash3.digest('hex');

    // 将指纹作为 kid 返回
    // return certFingerprint;
}

function getNowInBeijing() {
    // 返回"YYYY-MM-DD HH:mm:ss"格式的北京时间
    return moment().tz("Asia/Shanghai").format("YYYY-MM-DD HH:mm:ss");
}

function getNextUsageResetDate() {
    // 当前是几号
    const now = moment().tz("Asia/Shanghai");
    const day = now.date();

    // 下个月
    const nextMonth = now.clone().add(1, 'month').date(1);

    // 下月天数
    const daysInNextMonth = nextMonth.daysInMonth();

    // 如果下月有同样日号
    let resetDay = day <= daysInNextMonth ? day : daysInNextMonth;

    // 目标日期
    const nextResetDate = nextMonth.clone().date(resetDay);

    return nextResetDate.format("YYYY-MM-DD HH:mm:ss");
}
async function generateInviteCode() {
    const characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    const letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    const numbers = '0123456789';

    let inviteCode;
    let isUnique = false;

    while (!isUnique) {
        // 至少1位大写字母
        const firstChar = letters[Math.floor(Math.random() * letters.length)];
        // 至少1位数字
        const secChar = numbers[Math.floor(Math.random() * numbers.length)];
        let arr = [firstChar, secChar];

        // 剩下4位随机填充
        for (let i = 0; i < 4; i++) {
            arr.push(characters[Math.floor(Math.random() * characters.length)]);
        }
        // 洗牌，打乱顺序
        for (let i = arr.length - 1; i > 0; i--) {
            const j = Math.floor(Math.random() * (i + 1));
            [arr[i], arr[j]] = [arr[j], arr[i]];
        }
        inviteCode = arr.join('');

        // 检查是否唯一
        const checkSql = 'SELECT COUNT(*) AS count FROM transir_users WHERE invitation_code= ?';
        const result = await mysqlQuery(checkSql, [inviteCode]);
        if (result[0].count === 0) {
            isUnique = true;
        }
    }

    return inviteCode;
}
router.post('/user/account/delete', async (req, res) => {
    const {
        user_id
    } = req.body;
    if (!user_id) {
        return res.status(400).json({
            code: 400,
            msg: 'user_id必传'
        });
    }
    try {
        // 检查用户是否存在
        const rows = await mysqlQuery('SELECT user_id FROM transir_users WHERE user_id = ?', [user_id]);
        if (!rows.length) {
            return res.status(404).json({
                code: 404,
                msg: '账号不存在或已注销'
            });
        }
        // 注销（物理删除）
        await mysqlQuery('DELETE FROM transir_users WHERE user_id = ?', [user_id]);
        // 你可以按需还加上联删其它附属表内的用户数据（如Recipe_UserRecipe、Recipe_Collect等，需级联delete处理）
        return res.send({
            code: 0,
            msg: '账号已注销'
        });
    } catch (e) {
        console.error('[user/account/delete] 注销失败:', e);
        res.status(500).json({
            code: 500,
            msg: '服务器异常'
        });
    }
});

router.post('/user/account/newDelete', async (req, res) => {
    const {
        user_id
    } = req.body;
    if (!user_id) {
        return res.status(400).json({
            code: 400,
            msg: 'user_id必传'
        });
    }
    try {
        // 查询用户全字段
        const userRows = await mysqlQuery('SELECT * FROM transir_users WHERE user_id = ?', [user_id]);
        if (!userRows.length) {
            return res.status(404).json({
                code: 404,
                msg: '账号不存在或已注销'
            });
        }
        const user = userRows[0];

        // 插入快照表，所有核心用量字段全部包含
        await mysqlQuery(`
            INSERT INTO transir_users_deleted
            (
              user_id, auth_type, wechat_identifier, apple_identifier, username, avatar_url,
              feature_minutes, feature_minutes_used, feature_minutes_left,
              photos, photos_used, photos_left,
              tokens, tokens_used, tokens_left,
              momo_tokens_total, momo_tokens_used, momo_tokens_left,
              float_copy, float_copy_used, float_copy_left,
              quick_text, quick_text_used, quick_text_left,
              regular_photos_used,
              invited_by, invitation_time
            ) VALUES (?, ?, ?, ?, ?, ?,
                      ?, ?, ?,
                      ?, ?, ?,
                      ?, ?, ?,
                      ?, ?, ?,
                      ?, ?, ?,
                      ?, ?, ?, ?,?, ?)
        `, [
            user.user_id,
            user.auth_type,
            user.wechat_identifier,
            user.apple_identifier,
            user.username,
            user.avatar_url,

            user.feature_minutes || 0,
            user.feature_minutes_used || 0,
            user.feature_minutes_left || 0,
            user.photos || 0,
            user.photos_used || 0,
            user.photos_left || 0,
            user.tokens || 0,
            user.tokens_used || 0,
            user.tokens_left || 0,
            user.momo_tokens_total || 0,
            user.momo_tokens_used || 0,
            user.momo_tokens_left || 0,
            user.float_copy || 0,
            user.float_copy_used || 0,
            user.float_copy_left || 0,
            user.quick_text || 0,
            user.quick_text_used || 0,
            user.quick_text_left || 0,
            user.regular_photos_used || 0,
            user.invited_by || null,
            user.invitation_time || null,
        ]);
        // 然后再物理删除
        await mysqlQuery('DELETE FROM transir_users WHERE user_id=?', [user_id]);
        return res.send({
            code: 0,
            msg: '账号已注销'
        });
    } catch (e) {
        console.error('[user/account/delete] 注销失败:', e);
        res.status(500).json({
            code: 500,
            msg: '服务器异常'
        });
    }
});
/**
 * 从注销快照表 transir_users_deleted 恢复用户用量和被邀请关系
 * @param {Object} param0
 * @param {string} param0.user_id             - 当前用户ID
 * @param {string} param0.wechat_identifier   - 微信标识符
 * @param {string} param0.apple_identifier    - 苹果标识符
 */
async function recoverUserFromSnapshot({
    user_id,
    wechat_identifier,
    apple_identifier
}) {
    // 查找快照表中，最近一次该微信/苹果注销记录（只查一条最新的）
    const rows = await mysqlQuery(
        `SELECT * FROM transir_users_deleted
         WHERE (apple_identifier = ? AND apple_identifier IS NOT NULL)
            OR (wechat_identifier = ? AND wechat_identifier IS NOT NULL)
         ORDER BY deleted_at DESC LIMIT 1`,
        [apple_identifier || null, wechat_identifier || null]
    );

    // 没有找到快照，直接返回
    if (!rows.length) return;

    // 取出最近的一条快照记录
    const old = rows[0];

    // 恢复用量和邀请关系字段
    await mysqlQuery(
        `UPDATE transir_users SET
            feature_minutes = ?,
            feature_minutes_used = ?,
            feature_minutes_left = ?,
            photos = ?,
            photos_used = ?,
            photos_left = ?,
            tokens = ?,
            tokens_used = ?,
            tokens_left = ?,
            momo_tokens_total = ?,
            momo_tokens_used = ?,
            momo_tokens_left = ?,
            float_copy = ?,
            float_copy_used = ?,
            float_copy_left = ?,
            quick_text = ?,
            quick_text_used = ?,
            quick_text_left = ?,
            regular_photos_used = ?,
            invited_by = ?,              
            invitation_time = ?          
         WHERE user_id = ?`,
        [
            old.feature_minutes || 0,
            old.feature_minutes_used || 0,
            old.feature_minutes_left || 0,
            old.photos || 0,
            old.photos_used || 0,
            old.photos_left || 0,
            old.tokens || 0,
            old.tokens_used || 0,
            old.tokens_left || 0,
            old.momo_tokens_total || 0,
            old.momo_tokens_used || 0,
            old.momo_tokens_left || 0,
            old.float_copy || 0,
            old.float_copy_used || 0,
            old.float_copy_left || 0,
            old.quick_text || 0,
            old.quick_text_used || 0,
            old.quick_text_left || 0,
            old.regular_photos_used || 0,
            old.invited_by || null,
            old.invitation_time || null,
            user_id
        ]
    );
}

// router.post('/momo/consume', async (req, res) => {
//     const { user_id, consume_type, quantity } = req.body;
//     if (!user_id || !consume_type || quantity == null || isNaN(quantity) || quantity <= 0) {
//         return res.status(400).json({ code: 400, msg: '参数不合法' });
//     }

//     // 查系统配置（只读，无需事务保障）
//     const [config] = await mysqlQuery('SELECT * FROM transir_system_configs LIMIT 1', []);
//     if (!config) return res.status(500).json({ code: 500, msg: '系统配置不存在！' });

//     // 后台动态核算本次应消耗momo币
//     let momo_amount = 0;
//     let detail_desc = '';
//     switch (consume_type) {
//         case 'redeem_tokens':
//             momo_amount = Number(quantity) * 10; // 每10momo为一份
//             detail_desc = `兑换${Number(quantity) * config.redeem_tokens_per_10_momo || 0} tokens`;
//             break;
//         case 'web_capture':
//             momo_amount = Number(quantity) * (config.web_capture_per_piece || 0);
//             detail_desc = `网页截图屏翻${quantity}张`;
//             break;
//         case 'float_copy':
//             momo_amount = Number(quantity) * (config.float_copy_per_time || 0);
//             detail_desc = `悬浮复制屏翻${quantity}次`;
//             break;
//         case 'quick_capture':
//             momo_amount = Number(quantity) * (config.quick_capture_per_piece || 0);
//             detail_desc = `快捷截图屏翻${quantity}张`;
//             break;
//         case 'quick_text':
//             momo_amount = Number(quantity) * (config.quick_text_per_time || 0);
//             detail_desc = `快捷文字屏翻${quantity}次`;
//             break;
//         case 'accurate_minute':
//             const total_seconds = Number(quantity);
//             const minutes = Math.ceil(total_seconds / 60);
//             momo_amount = minutes * (config.accurate_minute_per_time || 0);
//             detail_desc = `跨应用精翻${minutes}分钟（${total_seconds}秒）`;
//             break;
//         default:
//             return res.status(400).json({ code: 400, msg: '未知消费类型' });
//     }
//     if (momo_amount <= 0) {
//         return res.status(400).json({ code: 400, msg: '扣除金额配置错误，请管理员检查系统配置' });
//     }

//     // ========== 事务部分开始 ==========
//     // 独立获得一个连接，用于事务
//     let conn = null;
//     try {
//         conn = await new Promise((resolve, reject) => {
//             poolmomo.getConnection((err, connection) => err ? reject(err) : resolve(connection));
//         });

//         // 开启事务
//         await new Promise((resolve, reject) => conn.beginTransaction(err => err ? reject(err) : resolve()));

//         // 查询最新实际余额（在事务内查，保证一致性）
//         const [balanceRow] = await mysqlQueryWithConn(
//             'SELECT SUM(remain) AS momo_balance FROM transir_recharge_records WHERE user_id=? AND status="unused" AND (expire_time IS NULL OR expire_time > NOW())',
//             [user_id], conn
//         );
//         const momo_balance = balanceRow && balanceRow.momo_balance ? Number(balanceRow.momo_balance) : 0;
//         if (momo_balance < momo_amount) {
//             await conn.release();
//             return res.send({
//                 code: 201, msg: 'momo币余额不足'
//             })

//         }

//         // ------------------------------------
//         // 分批扣除充值记录
//         let remain_to_consume = momo_amount;
//         const rechargeRows = await mysqlQueryWithConn(
//             'SELECT id, remain, expire_time FROM transir_recharge_records WHERE user_id=? AND status="unused" AND (expire_time IS NULL OR expire_time > NOW()) ORDER BY expire_time ASC, id ASC',
//             [user_id], conn
//         );
//         const consume_detail = [];
//         for (let i = 0; i < rechargeRows.length && remain_to_consume > 0; i++) {
//             const rec = rechargeRows[i];
//             const use = Math.min(remain_to_consume, rec.remain);
//             remain_to_consume -= use;
//             const new_remain = rec.remain - use;
//             const new_status = new_remain === 0 ? 'usedUp' : 'unused';
//             await mysqlQueryWithConn(
//                 'UPDATE transir_recharge_records SET remain=?, status=? WHERE id=? AND user_id=?',
//                 [new_remain, new_status, rec.id, user_id], conn
//             );
//             consume_detail.push({ recharge_id: rec.id, used: use });
//         }
//         if (remain_to_consume > 0) throw new Error('余额计算错误，请刷新后重试');

//         // 刷新用户表余额（同步）
//         await mysqlQueryWithConn(
//             'UPDATE transir_users SET momo_balance=momo_balance-? WHERE user_id=?',
//             [momo_amount, user_id], conn
//         );
//         // 写消费记录
//         await mysqlQueryWithConn(
//             'INSERT INTO momo_consume_records (user_id, consume_amount, consume_detail, consume_time, type, detail_desc) VALUES (?, ?, ?, NOW(), ?, ?)',
//             [user_id, momo_amount, JSON.stringify(consume_detail), consume_type, detail_desc], conn
//         );
//         // 提交事务
//         await new Promise((resolve, reject) => conn.commit(err => err ? reject(err) : resolve()));

//         // 查询最新余额可以用你的mysqlQuery（非强事务，只用于显示）
//         // const latestUserRows = await mysqlQuery('SELECT momo_balance FROM transir_users WHERE user_id=?', [user_id]);
//         await conn.release();

//         return res.send({
//             code: 0,
//             msg: 'success'
//             // data: {
//             //     user_id,
//             //     momo_balance: latestUserRows && latestUserRows[0] ? latestUserRows[0].momo_balance : null,
//             //     consume_type, quantity, momo_amount, detail_desc, consume_detail
//             // }
//         });
//     } catch (e) {
//         if (conn) await new Promise(resolve => conn.rollback(() => resolve()));
//         if (conn) conn.release();
//         console.error('momo/consume系统异常', e);
//         res.status(500).json({ code: 500, msg: '服务异常，操作失败' });
//     }
// });
/**
 * 事务专用SQL执行方法
 * @param {string} sql - 查询语句
 * @param {Array} arr - 参数数组
 * @param {object} conn - 来自poolmomo.getConnection获得的事务连接
 */
async function mysqlQueryWithConn(sql, arr, conn) {
    return new Promise((resolve, reject) => {
        conn.query(sql, arr, (err, results, fields) => {
            if (err) reject(err);
            else resolve(results);
        });
    });
}

router.post('/resource/use', async (req, res) => {
    // 1. 结构赋值参数，前端只要传需要的功能量即可，支持五大功能+tokens兑换
    const {
        user_id, // 基础业务主键
        accurate_minute, // 精翻秒数
        quick_translate_minute, //快翻模式秒数
        comic_realtime_minute,//漫画实时翻译秒数
        web_capture, // 网页截图张数
        quick_capture, // 快捷截图张数
        redeem_tokens, // tokens消耗量
        float_copy, // 悬浮复制屏翻次数
        quick_text, // 快捷文字屏翻次数
        video_export, // <--- 新增
        interpretation_refined, // 同声传译精翻
        interpretation_quick // 同声传译快翻
    } = req.body;
    if (!user_id) return res.status(400).json({
        code: 400,
        message: '缺少user_id参数'
    });
    // ================= [1] 启动事务 =================
    let conn = null;
    try {
        // 2. 申请数据库连接对象conn & 启动MySQL事务
        conn = await new Promise((resolve, reject) => {
            poolmomo.getConnection((err, c) => err ? reject(err) : resolve(c));
        });
        await new Promise((resolve, reject) => conn.beginTransaction(err => err ? reject(err) : resolve()));
        await mysqlQueryWithConn(
            'UPDATE transir_users SET feature_used = 1 WHERE user_id = ? AND feature_used = 0',
            [user_id], conn
        );
        // 3. 查询用户数据表，查所有可用资源“主包”用量
        let [user] = await mysqlQueryWithConn('SELECT * FROM transir_users WHERE user_id=? LIMIT 1', [
            user_id
        ], conn);
        if (!user) {
            await conn.rollback();
            conn.release();
            return res.status(404).json({
                code: 404,
                message: '用户不存在'
            });
        }
        // 4. 查系统业务配置，包含各类功能的主包和momo币兑换/功能扣币单价
        const [config] = await mysqlQueryWithConn('SELECT * FROM transir_system_configs LIMIT 1', [], conn);
        if (!config) {
            await conn.rollback();
            conn.release();
            return res.status(500).json({
                code: 500,
                message: '系统配置不存在！'
            });
        }
        //查询刷新过期
        await refreshExpiredMomoRecharge(user_id, conn);
        // 新增：将 feature_used 置为1（已使用任一功能）

        // 解析配置里的单价字段为小数
        const accurateMinuteUnit = parseFloat(config.accurate_minute_per_time) || 0; // 精翻每分钟momo币价格
        const quickTranslateMinuteUnit = parseFloat(config.quick_translate_minute_per_time) || 0; // 快翻每分钟momo币价格
        const comicRealtimeMinuteUnit = parseFloat(config.comic_realtime_minute_per_time) || 0; // 漫画实时翻译每分钟momo币价格
        const webCaptureUnit = parseFloat(config.web_capture_per_piece) || 0; // 网页截图每张momo币
        // const webCaptureUnit = 1.3;              // 网页截图每张momo币
        const quickCaptureUnit = parseFloat(config.quick_capture_per_piece) || 0; // 快捷截图每张momo币
        const floatCopyUnit = parseFloat(config.float_copy_per_time) || 0; // 悬浮复制每次momo币
        const quickTextUnit = parseFloat(config.quick_text_per_time) || 0; // 快捷文字每次momo币



        // ================ [2] 初始化这些累加器，用于后续update和接口返回 =================
        let updateFragments = [];
        let updateValues = [];
        let consumeDetailArr = []; // 记录每种资源消耗明细，方便返回给前端和日志记录

        // ================ [NEW] 统计数据收集器 =================
        let usageStatsArr = []; // 【新增】收集统计数据，格式：{feature_type, usage_amount, usage_count}
        const currentDate = moment().tz('Asia/Shanghai').format('YYYY-MM-DD');  // 当前日期 YYYY-MM-DD
        // ==================  同声传译快翻 interpretation_quick（单位：秒） ==================
        if (interpretation_quick !== undefined && !isNaN(interpretation_quick) && Number(interpretation_quick) > 0) {
            // 【新增】快翻模式：从用户表获取快翻模式余量
            const quickTranslateMinutesLeft = user.quick_translate_minutes_left || 0; // 【新增】剩余快翻模式时长

            let remainToUse = Number(interpretation_quick); // 用户本次需要消耗的秒数
            let quickTranslateBagUsed = 0; // 【新增】本次快翻模式包消耗的量
            let featureMinutesBagUsed = 0; // 本次精翻主包消耗的量
            let momoConsumeUsed = 0; // 本次要靠 momo币 补扣的资源量
            let momoAmountCost = 0; // 本次消耗资源时实际需要消耗的momo币总数

            // 【新增】优先使用快翻模式额度
            if (quickTranslateMinutesLeft >= remainToUse) {
                quickTranslateBagUsed = remainToUse;
                remainToUse = 0;
            } else {
                quickTranslateBagUsed = quickTranslateMinutesLeft;
                remainToUse -= quickTranslateMinutesLeft;
            }

            // 【新增】快翻模式额度用完后，使用精翻主包
            if (remainToUse > 0) {
                const featureMinutesLeftSec = user.feature_minutes_left || 0;
                if (featureMinutesLeftSec >= remainToUse) {
                    featureMinutesBagUsed = remainToUse;
                    remainToUse = 0;
                } else {
                    featureMinutesBagUsed = featureMinutesLeftSec;
                    remainToUse -= featureMinutesLeftSec;
                }
            }

            // 【新增】最后不够的部分用momo币补
            if (remainToUse > 0) {
                momoConsumeUsed = remainToUse;
            }

            // 【新增】更新快翻模式使用量到用户表
            if (quickTranslateBagUsed > 0) {
                const oldQuickTranslateUsed = user.quick_translate_minutes_used || 0;
                const newQuickTranslateUsed = oldQuickTranslateUsed + quickTranslateBagUsed;
                updateFragments.push('quick_translate_minutes_used=?', 'quick_translate_minutes_left=?');
                updateValues.push(newQuickTranslateUsed, Math.max(0, (user.quick_translate_minutes || 0) - newQuickTranslateUsed));
            }

            // 【修改】更新精翻主包使用量（如果有使用）
            if (featureMinutesBagUsed > 0) {
                const oldUsed = user.feature_minutes_used || 0;
                const usedNow = oldUsed + featureMinutesBagUsed;
                updateFragments.push('feature_minutes_used=?', 'feature_minutes_left=?');
                updateValues.push(usedNow, Math.max(0, (user.feature_minutes || 0) - usedNow));
            }

            // 【修改】超额部分用momo币扣费
            if (momoConsumeUsed > 0) {
                const minutePart = Math.ceil(momoConsumeUsed / 60);
                const momoAmount = toDecimal1(minutePart * quickTranslateMinuteUnit);
                let rr = await innerMomoConsume(user_id, "interpretation_quick", momoConsumeUsed, momoAmount, config, conn);
                if (!rr.success) {
                    await conn.rollback();
                    conn.release();
                    return res.json(rr);
                }
                momoAmountCost = momoAmount;
            }

            // 【新增】记录快翻模式消耗明细
            consumeDetailArr.push({
                resource: 'interpretation_quick',
                quick_translate_bag_used_seconds: quickTranslateBagUsed,
                feature_minutes_bag_used_seconds: featureMinutesBagUsed,
                momo_extra_used_seconds: momoConsumeUsed,
                momo_amount_cost: momoAmountCost
            });
            // ================ [NEW] 收集统计数据 =================
            usageStatsArr.push({
                feature_type: 'interpretation_quick',
                usage_amount: Number(interpretation_quick), // 使用的秒数
                usage_count: 1 // 使用次数
            });
        }
        // ==================  同声传译精翻：interpretation_refined（单位：秒） ==================
        if (interpretation_refined !== undefined && !isNaN(interpretation_refined) && Number(interpretation_refined) > 0) {
            // feature_minutes_left 现存可用时长，单位是秒
            const featureMinutesLeftSec = user.feature_minutes_left || 0;
            let remainToUse = Number(interpretation_refined); // 用户本次需要消耗的秒数
            let mainBagUsed = 0,
                momoConsumeUsed = 0,
                momoAmountCost = 0;
            //mainBagUsed  本次主包（基础资源包）被实际消耗掉的量
            //momoConsumeUsed  本次要靠 momo币 补扣的资源量
            //momoAmountCost  本次消耗资源时实际需要消耗的momo币总数
            // 优先用主包秒数，不够部分用momo币补
            if (featureMinutesLeftSec >= remainToUse) {
                mainBagUsed = remainToUse;
            } else {
                mainBagUsed = featureMinutesLeftSec;
                momoConsumeUsed = remainToUse - featureMinutesLeftSec;
            }


            // 主包可扣部分直接累计“已用秒数”，剩余部分也直接按秒处理，全部不再用分钟换算
            if (mainBagUsed > 0) {
                // 直接累加已用秒数，剩余秒数对应扣掉
                const oldUsed = user.feature_minutes_used || 0;
                const usedNow = oldUsed + mainBagUsed;
                updateFragments.push('feature_minutes_used=?', 'feature_minutes_left=?');
                updateValues.push(usedNow, Math.max(0, (user.feature_minutes || 0) - usedNow));
            }
            // 超额部分，按【秒=>分钟进位】进行momo币扣费，但用户资源只以秒为单位减少
            if (momoConsumeUsed > 0) {
                // momo币消耗与配置一致：多少“分钟”为一单位计价，按照秒数向上进位
                // 按秒数向上取整到分钟后乘以单价，并保留一位小数
                const minutePart = Math.ceil(momoConsumeUsed / 60);
                const momoAmountCost = toDecimal1(minutePart * comicRealtimeMinuteUnit);


                let rr = await innerMomoConsume(user_id, "interpretation_refined", momoConsumeUsed, momoAmountCost,
                    config, conn);
                if (!rr.success) {
                    await conn.rollback();
                    conn.release();
                    return res.json(rr);
                }
            }
            // 明细
            consumeDetailArr.push({
                resource: 'interpretation_refined',
                mainbag_used_seconds: mainBagUsed,
                momo_extra_used_seconds: momoConsumeUsed,
                momo_amount_cost: momoAmountCost
            });

            // ================ [NEW] 收集统计数据 =================
            usageStatsArr.push({
                feature_type: 'interpretation_refined',
                usage_amount: Number(interpretation_refined), // 使用的秒数
                usage_count: 1 // 使用次数
            });
        }
        // ==================  漫画实时翻译模式优先处理：comic_realtime_minute（单位：秒） ==================
        if (comic_realtime_minute !== undefined && !isNaN(comic_realtime_minute) && Number(comic_realtime_minute) > 0) {
            // feature_minutes_left 现存可用时长，单位是秒
            const featureMinutesLeftSec = user.feature_minutes_left || 0;
            let remainToUse = Number(comic_realtime_minute); // 用户本次需要消耗的秒数
            let mainBagUsed = 0,
                momoConsumeUsed = 0,
                momoAmountCost = 0;
            //mainBagUsed  本次主包（基础资源包）被实际消耗掉的量
            //momoConsumeUsed  本次要靠 momo币 补扣的资源量
            //momoAmountCost  本次消耗资源时实际需要消耗的momo币总数
            // 优先用主包秒数，不够部分用momo币补
            if (featureMinutesLeftSec >= remainToUse) {
                mainBagUsed = remainToUse;
            } else {
                mainBagUsed = featureMinutesLeftSec;
                momoConsumeUsed = remainToUse - featureMinutesLeftSec;
            }


            // 主包可扣部分直接累计“已用秒数”，剩余部分也直接按秒处理，全部不再用分钟换算
            if (mainBagUsed > 0) {
                // 直接累加已用秒数，剩余秒数对应扣掉
                const oldUsed = user.feature_minutes_used || 0;
                const usedNow = oldUsed + mainBagUsed;
                updateFragments.push('feature_minutes_used=?', 'feature_minutes_left=?');
                updateValues.push(usedNow, Math.max(0, (user.feature_minutes || 0) - usedNow));
            }
            // 超额部分，按【秒=>分钟进位】进行momo币扣费，但用户资源只以秒为单位减少
            if (momoConsumeUsed > 0) {
                // momo币消耗与配置一致：多少“分钟”为一单位计价，按照秒数向上进位
                // 按秒数向上取整到分钟后乘以单价，并保留一位小数
                const minutePart = Math.ceil(momoConsumeUsed / 60);
                const momoAmountCost = toDecimal1(minutePart * comicRealtimeMinuteUnit);
                let rr = await innerMomoConsume(user_id, "comic_realtime_minute", momoConsumeUsed, momoAmountCost,
                    config, conn);
                if (!rr.success) {
                    await conn.rollback();
                    conn.release();
                    return res.json(rr);
                }
            }
            // 明细
            consumeDetailArr.push({
                resource: 'comic_realtime_minute',
                mainbag_used_seconds: mainBagUsed,
                momo_extra_used_seconds: momoConsumeUsed,
                momo_amount_cost: momoAmountCost
            });

            // ================ [NEW] 收集统计数据 =================
            usageStatsArr.push({
                feature_type: 'comic_realtime_minute',
                usage_amount: Number(comic_realtime_minute), // 使用的秒数
                usage_count: 1 // 使用次数
            });
        }

        // ================== [3.1] 【新增】快翻模式优先处理：quick_translate_minutes_used/left（单位：秒） ==================
        if (quick_translate_minute !== undefined && !isNaN(quick_translate_minute) && Number(quick_translate_minute) > 0) {
            // 【新增】快翻模式：从用户表获取快翻模式余量
            const quickTranslateMinutesLeft = user.quick_translate_minutes_left || 0; // 【新增】剩余快翻模式时长

            let remainToUse = Number(quick_translate_minute); // 用户本次需要消耗的秒数
            let quickTranslateBagUsed = 0; // 【新增】本次快翻模式包消耗的量
            let featureMinutesBagUsed = 0; // 本次精翻主包消耗的量
            let momoConsumeUsed = 0; // 本次要靠 momo币 补扣的资源量
            let momoAmountCost = 0; // 本次消耗资源时实际需要消耗的momo币总数

            // 【新增】优先使用快翻模式额度
            if (quickTranslateMinutesLeft >= remainToUse) {
                quickTranslateBagUsed = remainToUse;
                remainToUse = 0;
            } else {
                quickTranslateBagUsed = quickTranslateMinutesLeft;
                remainToUse -= quickTranslateMinutesLeft;
            }

            // 【新增】快翻模式额度用完后，使用精翻主包
            if (remainToUse > 0) {
                const featureMinutesLeftSec = user.feature_minutes_left || 0;
                if (featureMinutesLeftSec >= remainToUse) {
                    featureMinutesBagUsed = remainToUse;
                    remainToUse = 0;
                } else {
                    featureMinutesBagUsed = featureMinutesLeftSec;
                    remainToUse -= featureMinutesLeftSec;
                }
            }

            // 【新增】最后不够的部分用momo币补
            if (remainToUse > 0) {
                momoConsumeUsed = remainToUse;
            }

            // 【新增】更新快翻模式使用量到用户表
            if (quickTranslateBagUsed > 0) {
                const oldQuickTranslateUsed = user.quick_translate_minutes_used || 0;
                const newQuickTranslateUsed = oldQuickTranslateUsed + quickTranslateBagUsed;
                updateFragments.push('quick_translate_minutes_used=?', 'quick_translate_minutes_left=?');
                updateValues.push(newQuickTranslateUsed, Math.max(0, (user.quick_translate_minutes || 0) - newQuickTranslateUsed));
            }

            // 【修改】更新精翻主包使用量（如果有使用）
            if (featureMinutesBagUsed > 0) {
                const oldUsed = user.feature_minutes_used || 0;
                const usedNow = oldUsed + featureMinutesBagUsed;
                updateFragments.push('feature_minutes_used=?', 'feature_minutes_left=?');
                updateValues.push(usedNow, Math.max(0, (user.feature_minutes || 0) - usedNow));
            }

            // 【修改】超额部分用momo币扣费
            if (momoConsumeUsed > 0) {
                const minutePart = Math.ceil(momoConsumeUsed / 60);
                const momoAmount = toDecimal1(minutePart * quickTranslateMinuteUnit);
                let rr = await innerMomoConsume(user_id, "quick_translate_minute", momoConsumeUsed, momoAmount, config, conn);
                if (!rr.success) {
                    await conn.rollback();
                    conn.release();
                    return res.json(rr);
                }
                momoAmountCost = momoAmount;
            }

            // 【新增】记录快翻模式消耗明细
            consumeDetailArr.push({
                resource: 'quick_translate_minute',
                quick_translate_bag_used_seconds: quickTranslateBagUsed,
                feature_minutes_bag_used_seconds: featureMinutesBagUsed,
                momo_extra_used_seconds: momoConsumeUsed,
                momo_amount_cost: momoAmountCost
            });
            // ================ [NEW] 收集统计数据 =================
            usageStatsArr.push({
                feature_type: 'quick_translate_minute',
                usage_amount: Number(quick_translate_minute), // 使用的秒数
                usage_count: 1 // 使用次数
            });
        }
        // ================== [3.1] 精翻：feature_minutes_used/left（单位：秒） ==================
        if (accurate_minute !== undefined && !isNaN(accurate_minute) && Number(accurate_minute) > 0) {
            // feature_minutes_left 现存可用时长，单位是秒
            const featureMinutesLeftSec = user.feature_minutes_left || 0;
            let remainToUse = Number(accurate_minute); // 用户本次需要消耗的秒数
            let mainBagUsed = 0,
                momoConsumeUsed = 0,
                momoAmountCost = 0;
            //mainBagUsed  本次主包（基础资源包）被实际消耗掉的量
            //momoConsumeUsed  本次要靠 momo币 补扣的资源量
            //momoAmountCost  本次消耗资源时实际需要消耗的momo币总数
            // 优先用主包秒数，不够部分用momo币补
            if (featureMinutesLeftSec >= remainToUse) {
                mainBagUsed = remainToUse;
            } else {
                mainBagUsed = featureMinutesLeftSec;
                momoConsumeUsed = remainToUse - featureMinutesLeftSec;
            }


            // 主包可扣部分直接累计“已用秒数”，剩余部分也直接按秒处理，全部不再用分钟换算
            if (mainBagUsed > 0) {
                // 直接累加已用秒数，剩余秒数对应扣掉
                const oldUsed = user.feature_minutes_used || 0;
                const usedNow = oldUsed + mainBagUsed;
                updateFragments.push('feature_minutes_used=?', 'feature_minutes_left=?');
                updateValues.push(usedNow, Math.max(0, (user.feature_minutes || 0) - usedNow));
            }
            // 超额部分，按【秒=>分钟进位】进行momo币扣费，但用户资源只以秒为单位减少
            if (momoConsumeUsed > 0) {
                // momo币消耗与配置一致：多少“分钟”为一单位计价，按照秒数向上进位
                // 按秒数向上取整到分钟后乘以单价，并保留一位小数
                const minutePart = Math.ceil(momoConsumeUsed / 60);
                const momoAmountCost = toDecimal1(minutePart * accurateMinuteUnit);
                let rr = await innerMomoConsume(user_id, "accurate_minute", momoConsumeUsed, momoAmountCost,
                    config, conn);
                if (!rr.success) {
                    await conn.rollback();
                    conn.release();
                    return res.json(rr);
                }
            }
            // 明细
            consumeDetailArr.push({
                resource: 'feature_minutes',
                mainbag_used_seconds: mainBagUsed,
                momo_extra_used_seconds: momoConsumeUsed,
                momo_amount_cost: momoAmountCost
            });

            // ================ [NEW] 收集统计数据 =================
            usageStatsArr.push({
                feature_type: 'accurate_minute',
                usage_amount: Number(accurate_minute), // 使用的秒数
                usage_count: 1 // 使用次数
            });
        }

        // ================== [3.2] 照片用量，聚合web_capture/quick_capture，优先扣photos主包 ==================
        let totalPhotosNeed = 0;
        if (web_capture !== undefined && !isNaN(web_capture) && Number(web_capture) > 0) totalPhotosNeed +=
            Number(web_capture);
        if (quick_capture !== undefined && !isNaN(quick_capture) && Number(quick_capture) > 0)
            totalPhotosNeed += Number(quick_capture);

        if (totalPhotosNeed > 0) {
            const oldUsed = user.photos_used || 0;
            const photosLeft = user.photos_left || 0;
            let mainBagUsed = 0,
                momoConsumeUsed = 0,
                momoAmountCost = 0;
            if (photosLeft >= totalPhotosNeed) {
                // 主包够用
                mainBagUsed = totalPhotosNeed;
            } else {
                // 主包清零，多余的用momo币分销web和quick
                mainBagUsed = photosLeft;
                momoConsumeUsed = totalPhotosNeed - photosLeft;
            }
            // 主包扣量
            if (mainBagUsed > 0) {
                const curUsed = oldUsed + mainBagUsed;
                updateFragments.push('photos_used=?', 'photos_left=?');
                updateValues.push(curUsed, Math.max(0, (user.photos || 0) - curUsed));
            }
            // 超出部分分别依赖web_capture/quick_capture的momo价格再单独批扣
            if (momoConsumeUsed > 0) {
                // 先web，后quick
                if (web_capture && web_capture > 0) {
                    let webOver = Math.max(0, +web_capture - (user.photos_left || 0));
                    if (webOver > 0) {
                        let momoAmount = toDecimal1(webOver * webCaptureUnit);
                        let rr = await innerMomoConsume(user_id, "web_capture", webOver, momoAmount, config,
                            conn);
                        if (!rr.success) {
                            await conn.rollback();
                            conn.release();
                            return res.json(rr);
                        }
                        momoAmountCost = toDecimal1(momoAmountCost + momoAmount);
                    }
                }
                if (quick_capture && quick_capture > 0) {
                    let quickOver = Math.max(0, +quick_capture - Math.max(0, (user.photos_left || 0) - (
                        web_capture || 0)));
                    if (quickOver > 0) {
                        let momoAmount = toDecimal1(quickOver * quickCaptureUnit);
                        let rr = await innerMomoConsume(user_id, "quick_capture", quickOver, momoAmount,
                            config, conn);
                        if (!rr.success) {
                            await conn.rollback();
                            conn.release();
                            return res.json(rr);
                        }
                        momoAmountCost = toDecimal1(momoAmountCost + momoAmount);
                    }
                }
            }
            // ============<<< 新增部分 begin  >>>==============
            // 如果是普通用户，把本次拍照用量累加到 regular_photos_used
            if (user.user_type === 'regular') {
                // 先查出原有 regular_photos_used，如果表没有默认加 || 0
                const oldRegularUsed = user.regular_photos_used || 0;
                // 新的 regular_photos_used
                const newRegularUsed = oldRegularUsed + totalPhotosNeed;
                updateFragments.push('regular_photos_used=?');
                updateValues.push(newRegularUsed);
            }
            // ============<<< 新增部分 end  >>>==============

            consumeDetailArr.push({
                resource: 'photos',
                mainbag_used: mainBagUsed,
                momo_extra_used: momoConsumeUsed,
                momo_amount_cost: momoAmountCost
            });


            // ================ [NEW] 收集统计数据 - 分别统计web_capture和quick_capture =================
            if (web_capture !== undefined && !isNaN(web_capture) && Number(web_capture) > 0) {
                usageStatsArr.push({
                    feature_type: 'web_capture',
                    usage_amount: Number(web_capture), // 使用的张数
                    usage_count: 1 // 使用次数
                });
            }
            if (quick_capture !== undefined && !isNaN(quick_capture) && Number(quick_capture) > 0) {
                usageStatsArr.push({
                    feature_type: 'quick_capture',
                    usage_amount: Number(quick_capture), // 使用的张数
                    usage_count: 1 // 使用次数
                });
            }
        }

        // ================== [3.3] tokens主包优先，其次消耗momo兑换包 ==================
        if (redeem_tokens !== undefined && !isNaN(redeem_tokens) && Number(redeem_tokens) > 0) {
            let need = Number(redeem_tokens); // 本次希望消耗tokens数量
            let tokensLeft = user.tokens_left || 0;
            let momoTokensLeft = user.momo_tokens_left || 0;
            let mainBagUsed = 0,
                momoBagUsed = 0;

            if (tokensLeft >= need) {
                // 主包够，直接扣主包
                mainBagUsed = need;
                momoBagUsed = 0;
            } else {
                // 主包扣完再扣momo兑换包，不要再用momo币再兑换
                mainBagUsed = tokensLeft;
                const remain = need - tokensLeft;
                if (momoTokensLeft >= remain) {
                    momoBagUsed = remain;
                } else {
                    // 主包与兑换包都不够，全部扣完仍不够，需报错
                    await conn.rollback();
                    conn.release();
                    return res.json({
                        code: 402,
                        msg: 'tokens用量不足，主包和兑换包都不够'
                    });
                }
            }
            // 主包扣
            if (mainBagUsed > 0) {
                const curUsed = (user.tokens_used || 0) + mainBagUsed;
                updateFragments.push('tokens_used=?', 'tokens_left=?');
                updateValues.push(curUsed, Math.max(0, (user.tokens || 0) - curUsed));
            }
            // 兑换包扣
            if (momoBagUsed > 0) {
                let curMomoUsed = (user.momo_tokens_used || 0) + momoBagUsed;
                updateFragments.push('momo_tokens_used=?', 'momo_tokens_left=?');
                updateValues.push(curMomoUsed, Math.max(0, momoTokensLeft - momoBagUsed));
            }
            consumeDetailArr.push({
                resource: 'tokens',
                mainbag_used: mainBagUsed,
                momo_bag_used: momoBagUsed
            });

            // ================ [NEW] 收集统计数据 =================
            usageStatsArr.push({
                feature_type: 'redeem_tokens',
                usage_amount: Number(redeem_tokens), // 使用的token数量
                usage_count: 1 // 使用次数
            });
        }

        // ================== [3.4] 悬浮复制主包优先，不够再补扣momo币 ==================
        if (float_copy !== undefined && !isNaN(float_copy) && Number(float_copy) > 0) {
            let need = Number(float_copy);
            let left = user.float_copy_left || 0;
            let mainBagUsed = 0,
                momoConsumeUsed = 0,
                momoAmountCost = 0;
            if (left >= need) {
                mainBagUsed = need;
                momoConsumeUsed = 0;
            } else {
                mainBagUsed = left;
                momoConsumeUsed = need - left;
            }
            if (mainBagUsed > 0) {
                let used = (user.float_copy_used || 0) + mainBagUsed;
                updateFragments.push('float_copy_used=?', 'float_copy_left=?');
                updateValues.push(used, Math.max(0, (user.float_copy || 0) - used));
            }
            if (momoConsumeUsed > 0) {
                let momoAmount = toDecimal1(momoConsumeUsed * floatCopyUnit);
                let rr = await innerMomoConsume(user_id, "float_copy", momoConsumeUsed, momoAmount, config,
                    conn);
                if (!rr.success) {
                    await conn.rollback();
                    conn.release();
                    return res.json(rr);
                }
                momoAmountCost = momoAmount;
            }
            consumeDetailArr.push({
                resource: 'float_copy',
                mainbag_used: mainBagUsed,
                momo_extra_used: momoConsumeUsed,
                momo_amount_cost: momoAmountCost
            });

            // ================ [NEW] 收集统计数据 =================
            usageStatsArr.push({
                feature_type: 'float_copy',
                usage_amount: Number(float_copy), // 使用的次数
                usage_count: 1 // 使用次数
            });
        }

        // ================== [3.5] 快捷文字主包优先，不够再用momo币 ==================
        if (quick_text !== undefined && !isNaN(quick_text) && Number(quick_text) > 0) {
            let need = Number(quick_text);
            let left = user.quick_text_left || 0;
            let mainBagUsed = 0,
                momoConsumeUsed = 0,
                momoAmountCost = 0;
            if (left >= need) {
                mainBagUsed = need;
                momoConsumeUsed = 0;
            } else {
                mainBagUsed = left;
                momoConsumeUsed = need - left;
            }
            if (mainBagUsed > 0) {
                let used = (user.quick_text_used || 0) + mainBagUsed;
                updateFragments.push('quick_text_used=?', 'quick_text_left=?');
                updateValues.push(used, Math.max(0, (user.quick_text || 0) - used));
            }
            if (momoConsumeUsed > 0) {
                let momoAmount = toDecimal1(momoConsumeUsed * quickTextUnit);
                let rr = await innerMomoConsume(user_id, "quick_text", momoConsumeUsed, momoAmount, config,
                    conn);
                if (!rr.success) {
                    await conn.rollback();
                    conn.release();
                    return res.json(rr);
                }
                momoAmountCost = momoAmount;
            }
            consumeDetailArr.push({
                resource: 'quick_text',
                mainbag_used: mainBagUsed,
                momo_extra_used: momoConsumeUsed,
                momo_amount_cost: momoAmountCost
            });

            // ================ [NEW] 收集统计数据 =================
            usageStatsArr.push({
                feature_type: 'quick_text',
                usage_amount: Number(quick_text), // 使用的次数
                usage_count: 1 // 使用次数
            });
        }
        // ================== [3.6] 视频导出功能主包消耗 ==================
        if (video_export !== undefined && !isNaN(video_export) && Number(video_export) > 0) {
            let need = Number(video_export);
            let left = user.video_export_count_left || 0;
            let mainBagUsed = 0;
            if (left < need) {
                await conn.rollback();
                conn.release();
                return res.json({
                    code: 402,
                    msg: '视频导出次数不足'
                });
            }
            mainBagUsed = need;
            // 主包扣量
            if (mainBagUsed > 0) {
                let used = (user.video_export_count_used || 0) + mainBagUsed;
                updateFragments.push('video_export_count_used=?', 'video_export_count_left=?');
                updateValues.push(used, Math.max(0, (user.video_export_count || 0) - used));
            }
            consumeDetailArr.push({
                resource: 'video_export',
                mainbag_used: mainBagUsed
            });
            // 日志统计
            usageStatsArr.push({
                feature_type: 'video_export',
                usage_amount: Number(video_export),
                usage_count: 1
            });
        }
        // ================== [4] 汇总批量更新用户（只更新变化过的字段，强一致事务保障） ==================
        if (updateFragments.length > 0) {
            updateFragments.push('updated_at=NOW()');
            let updateSql = `UPDATE transir_users SET ${updateFragments.join(', ')} WHERE user_id=?`;
            updateValues.push(user_id);
            await mysqlQueryWithConn(updateSql, updateValues, conn);
        }

        // ================ [NEW] 插入统计数据到实时上报表 =================
        if (usageStatsArr.length > 0) {
            for (let stat of usageStatsArr) {
                const insertLogSql = `
                    INSERT INTO transir_usage_logs 
                    (user_id, feature_type, usage_amount, usage_count, created_date) 
                    VALUES (?, ?, ?, ?, ?)
                `;
                await mysqlQueryWithConn(insertLogSql, [
                    user_id,
                    stat.feature_type,
                    stat.usage_amount,
                    stat.usage_count,
                    currentDate
                ], conn);
            }
        }

        // ================== [5] 查询最新用户状态，提交事务 ==================
        let [latestUser] = await mysqlQueryWithConn('SELECT * FROM transir_users WHERE user_id=?', [
            user_id
        ], conn);
        await new Promise((resolve, reject) => conn.commit(err => err ? reject(err) : resolve()));
        conn.release();

        // ================== [6] 返回操作完成结果和所有扣量明细，便于前端和稽核用 ==================
        res.json({
            code: 0,
            msg: 'success',
            data: latestUser,
            consume_detail: consumeDetailArr
        });
    } catch (e) {
        if (conn) {
            try {
                await conn.rollback();
                conn.release();
            } catch (e2) { }
        }
        // 控制台输出异常堆栈便于定位bug
        console.error(e);
        res.status(500).json({
            code: 500,
            msg: '服务器错误'
        });
    }
});



/**
 * momo币分笔消耗核心
 * @param user_id        用户ID
 * @param consume_type   消费类型字符串
 * @param quantity       本次消耗的 资源量（比如张数/次数/秒数，和类型相关）
 * @param momo_amount    本次应消耗的momo币（小数点后一位）
 * @param config         系统配置，部分类型多用于detail_desc拼接
 * @param conn           已开启事务的MySQL连接
 * @returns {Promise<{success:boolean, code?:number, msg?:string}>}
 */
async function innerMomoConsume(user_id, consume_type, quantity, momo_amount, config, conn) {
    // 1. 查询用户所有未过期&未用完充值单，总余额
    const [balRes] = await mysqlQueryWithConn(
        `SELECT SUM(remain) AS momo_balance 
         FROM transir_recharge_records 
         WHERE user_id=? AND status='unused' AND (expire_time IS NULL OR expire_time > NOW())`,
        [user_id], conn
    );
    // 避免空值直接parse，保证为float小数型
    const momo_balance = balRes && balRes.momo_balance ? parseFloat(balRes.momo_balance) : 0.0;
    // 2. 余额不足直接返回，事务由外层接口负责
    if (momo_balance < toDecimal1(momo_amount)) {
        return {
            success: false,
            code: 201,
            msg: 'momo币余额不足'
        };
    }

    // 3. 查询所有未用完&未过期充值记录，按到期时间优先分批扣除
    let remain_to_consume = toDecimal1(momo_amount); // 剩余待扣
    // 查出每笔未用完、未过期的充值包，按最早到期优先用
    const rechargeRows = await mysqlQueryWithConn(
        `SELECT id, remain 
         FROM transir_recharge_records 
         WHERE user_id=? AND status='unused' AND (expire_time IS NULL OR expire_time > NOW()) 
         ORDER BY expire_time ASC, id ASC`,
        [user_id], conn
    );
    // 明细记录分笔扣款方便对账
    const consume_detail = [];

    // 4. 批量分笔循环扣：永远按单笔最大可扣（防止溢出）
    for (let i = 0; i < rechargeRows.length && remain_to_consume > 0.0001; i++) {
        const rec = rechargeRows[i];
        // 取最多可用的金额，小数型，强制保留一位小数
        let usable = toDecimal1(rec.remain);
        let useThis = Math.min(remain_to_consume, usable);
        useThis = toDecimal1(useThis);
        // 更新剩余待扣
        remain_to_consume = toDecimal1(remain_to_consume - useThis);
        // 扣后该充值剩余多少
        let new_remain = toDecimal1(usable - useThis);
        // 状态判断
        const new_status = Math.abs(new_remain) < 0.0001 ? 'usedUp' : 'unused'; // 防止浮点误差误判
        // 4-1. 落库修改该充值单
        await mysqlQueryWithConn(
            'UPDATE transir_recharge_records SET remain=?, status=? WHERE id=? AND user_id=?',
            [new_remain, new_status, rec.id, user_id], conn
        );
        // 4-2. 记录分笔扣
        consume_detail.push({
            recharge_id: rec.id,
            used: useThis
        });
    }

    // 5. 并发极端场景下仍未扣满，直接拒绝向下，留给主流程回滚
    if (remain_to_consume > 0.0001) {
        return {
            success: false,
            code: 202,
            msg: '并发异常，扣款失败'
        };
    }

    // 6. 更新用户主表余额：用最新momo_balance减去本次消费量
    await mysqlQueryWithConn(
        'UPDATE transir_users SET momo_balance = ROUND(momo_balance - ?, 1) WHERE user_id = ?',
        [toDecimal1(momo_amount), user_id], conn
    );

    // 7. 拼业务消费类型描述，为后续账单、前端、溯源用
    let detail_desc = '';
    switch (consume_type) {
        case 'redeem_tokens':
            // 这里兑换tokens实际数量建议按兑换比例公式配置拼接
            detail_desc = `兑换${quantity * (parseFloat(config.redeem_tokens_per_10_momo) || 0)} tokens`;
            break;
        case 'web_capture':
            detail_desc = `网页截图屏翻${quantity}张`;
            break;
        case 'float_copy':
            detail_desc = `悬浮复制屏翻${quantity}次`;
            break;
        case 'quick_capture':
            detail_desc = `快捷截图屏翻${quantity}张`;
            break;
        case 'quick_text':
            detail_desc = `快捷文字屏翻${quantity}次`;
            break;
        case 'accurate_minute':
            detail_desc = `跨应用精翻${quantity}秒`;
            break;
        case 'quick_translate_minute':
            detail_desc = `跨应用快翻${quantity}秒`;
            break;
        case 'comic_realtime_minute':
            detail_desc = `漫画实时翻${quantity}秒`;
            break;
        default:
            detail_desc = `其他类型消耗${quantity}`;
    }

    // 8. 消费记录流水：所有金额小数，consume_detail存分笔json
    await mysqlQueryWithConn(
        `INSERT INTO transir_consume_records
            (user_id, consume_amount, consume_detail, consume_time, type, detail_desc)
         VALUES (?, ?, ?, NOW(), ?, ?)`,
        [
            user_id,
            toDecimal1(momo_amount), // 保留一位小数
            JSON.stringify(consume_detail),
            consume_type,
            detail_desc
        ],
        conn
    );

    // 9. 成功返回
    return {
        success: true
    };
}

// momo币兑换tokens接口
router.post('/exchange_tokens', async (req, res) => {
    // const { user_id, redeem_times } = req.body;
    // if (!user_id || !redeem_times || isNaN(redeem_times) || Number(redeem_times) < 1) {
    //     return res.status(400).json({ code: 400, msg: '用户id和兑换份数必须合法' });
    // }
    const {
        user_id
    } = req.body;
    if (!user_id) {
        return res.status(400).json({
            code: 400,
            msg: '用户id不合法'
        });
    }
    let redeem_times = 1;
    // 查当前兑换比例
    const [config] = await mysqlQuery('SELECT * FROM transir_system_configs LIMIT 1', []);
    if (!config || !config.redeem_tokens_per_10_momo) {
        return res.status(500).json({
            code: 500,
            msg: '兑换配置不存在'
        });
    }

    const oneUnitTokens = config.redeem_tokens_per_10_momo; // 每10momo币可兑换的tokens数
    const momo_amount = Number(redeem_times) * 10; // 一次需消耗的momo币
    const tokens_count = Number(redeem_times) * oneUnitTokens; // 一次实际兑换获得的tokens

    // 启动事务
    let conn = null;
    try {
        conn = await new Promise((resolve, reject) => {
            poolmomo.getConnection((err, c) => err ? reject(err) : resolve(c));
        });
        await new Promise((resolve, reject) => conn.beginTransaction(err => err ? reject(err) : resolve()));

        // 1. 校验余额是否足够（在事务内查，避免并发问题）
        const [balRes] = await mysqlQueryWithConn(
            'SELECT SUM(remain) AS momo_balance FROM transir_recharge_records WHERE user_id=? AND status="unused" AND (expire_time IS NULL OR expire_time > NOW())',
            [user_id], conn
        );
        const momo_balance = balRes && balRes.momo_balance ? Number(balRes.momo_balance) : 0;
        if (momo_balance < momo_amount) {
            await conn.rollback();
            conn.release();
            return res.json({
                code: 201,
                msg: 'momo币余额不足'
            });
        }

        // 2. 分批扣充值记录并生成consume_detail
        let remain_to_consume = momo_amount;
        const rechargeRows = await mysqlQueryWithConn(
            'SELECT id, remain FROM transir_recharge_records WHERE user_id=? AND status="unused" AND (expire_time IS NULL OR expire_time > NOW()) ORDER BY expire_time ASC, id ASC',
            [user_id], conn
        );
        const consume_detail = [];
        for (let i = 0; i < rechargeRows.length && remain_to_consume > 0; i++) {
            let rec = rechargeRows[i];
            let use = Math.min(remain_to_consume, rec.remain);
            remain_to_consume -= use;
            let new_remain = rec.remain - use;
            let new_status = new_remain === 0 ? 'usedUp' : 'unused';
            await mysqlQueryWithConn(
                'UPDATE transir_recharge_records SET remain=?, status=? WHERE id=? AND user_id=?',
                [new_remain, new_status, rec.id, user_id], conn
            );
            consume_detail.push({
                recharge_id: rec.id,
                used: use
            });
        }
        if (remain_to_consume > 0) {
            await conn.rollback();
            conn.release();
            return res.json({
                code: 202,
                msg: '并发异常，实际可用momo币不足'
            });
        }

        // 3. 更新用户momo_balance和tokens相关累加字段
        await mysqlQueryWithConn(
            'UPDATE transir_users SET momo_balance=momo_balance-?, momo_tokens_total=momo_tokens_total+?, momo_tokens_left=momo_tokens_left+? WHERE user_id=?',
            [momo_amount, tokens_count, tokens_count, user_id], conn
        );

        // 4. 生成momo消费&兑换记录
        await mysqlQueryWithConn(
            'INSERT INTO transir_consume_records(user_id, consume_amount, consume_detail, consume_time, type, detail_desc) VALUES (?, ?, ?, NOW(), ?, ?)',
            [
                user_id,
                momo_amount,
                JSON.stringify(consume_detail),
                'redeem_tokens',
                `兑换${tokens_count} tokens（${redeem_times}次)`
            ],
            conn
        );

        // 5. 提交事务，返回新余额和累计tokens
        let [user] = await mysqlQueryWithConn(
            'SELECT momo_balance, momo_tokens_total, momo_tokens_left FROM transir_users WHERE user_id=?',
            [user_id], conn);

        await new Promise((resolve, reject) => conn.commit(err => err ? reject(err) : resolve()));
        conn.release();

        return res.json({
            code: 0,
            msg: 'success',
            data: {
                momo_balance: user?.momo_balance ?? null,
                momo_tokens_total: user?.momo_tokens_total ?? null,
                momo_tokens_left: user?.momo_tokens_left ?? null,
                tokens_count
            }
        });
    } catch (e) {
        if (conn) {
            try {
                await conn.rollback();
                conn.release();
            } catch (e2) { }
        }
        console.error(e);
        return res.status(500).json({
            code: 500,
            msg: '服务器错误'
        });
    }
});
router.post('/recharge-test', async (req, res) => {
    const {
        user_id,
        amount,
        expire_time
    } = req.body;
    if (!user_id || !amount || isNaN(amount) || Number(amount) <= 0) {
        return res.status(400).json({
            code: 400,
            msg: '用户ID和充值金额必须合法'
        });
    }

    // 计算到期时间（如果前端没传，自动+1年）
    let expireTimeValue = null;
    {
        const now = new Date();
        now.setFullYear(now.getFullYear() + 1);
        // 加8小时，单位ms
        now.setTime(now.getTime() + 8 * 60 * 60 * 1000);
        // 转换为 'YYYY-MM-DD HH:mm:ss' 格式
        expireTimeValue = now.toISOString().slice(0, 19).replace('T', ' ');
    }

    let conn = null;
    try {
        // 1. 启动事务
        conn = await new Promise((resolve, reject) => {
            poolmomo.getConnection((err, c) => err ? reject(err) : resolve(c));
        });
        await new Promise((resolve, reject) => conn.beginTransaction(err => err ? reject(err) : resolve()));

        // 2. 写入充值记录
        const insertResult = await mysqlQueryWithConn(
            `INSERT INTO transir_recharge_records 
                (user_id, amount, remain, recharge_time, expire_time, status,type) 
             VALUES (?, ?, ?, NOW(), ?, 'unused',2)`,
            [user_id, amount, amount, expireTimeValue], conn
        );

        // 3. 更新用户表余额（momo_balance 增加本次充值额度）
        await mysqlQueryWithConn(
            `UPDATE transir_users SET momo_balance = momo_balance + ? WHERE user_id = ?`,
            [amount, user_id], conn
        );

        // 4. 查询最新用户余额
        const [user] = await mysqlQueryWithConn(
            `SELECT momo_balance FROM transir_users WHERE user_id=?`,
            [user_id], conn
        );

        // 5. 提交事务，返回本次充值单ID和新的余额
        await new Promise((resolve, reject) => conn.commit(err => err ? reject(err) : resolve()));
        conn.release();

        return res.json({
            code: 0,
            msg: 'success',
            data: {
                momo_balance: user ? user.momo_balance : null,
                amount,
                expire_time: expireTimeValue
            }
        });
    } catch (err) {
        if (conn) {
            try {
                await conn.rollback();
                conn.release();
            } catch (e2) { }
        }
        console.error(err);
        res.status(500).json({
            code: 500,
            msg: '服务器错误'
        });
    }
});
// 充值momo币
router.post('/recharge', async (req, res) => {
    const {
        user_id,
        transaction_id,
        pk,
        env
    } = req.body;
    if (!user_id || !transaction_id || !pk || !env) {
        return res.status(400).json({
            code: 400,
            msg: '缺少参数'
        });
    }
    console.log(req.body, '充值momo币')
    // 1. 构建 JWT Token（具体 createJWT 见 Apple 官方文档）
    let url = null;
    if (env == 'sanbox') {
        url = 'https://api.storekit-sandbox.itunes.apple.com/inApps/v1/transactions/'
    } else {
        url = 'https://api.storekit.itunes.apple.com/inApps/v1/transactions/'

    }
    const jwtToken = createJWT(pk);
    const consumptionUrl = `${url}${transaction_id}`;
    const configAxios = {
        headers: {
            Authorization: `Bearer ${jwtToken}`,
            'Content-Type': 'application/json',
        },
    };

    let conn = null;
    try {
        // 2. 请求苹果API校验交易
        const response = await axios.get(consumptionUrl, configAxios);
        if (!response || !response.data || !response.data.signedTransactionInfo) {
            console.log('充值momo币,苹果接口数据异常')
            return res.status(400).json({
                code: 400,
                msg: '苹果接口数据异常'
            });
        }

        const decoded = jwt.decode(response.data.signedTransactionInfo, {
            complete: true
        });
        const data = decoded && decoded.payload;
        console.log(data)
        if (!data) {
            console.log('充值momo币,交易凭据解析失败')
            return res.status(400).json({
                code: 400,
                msg: '交易凭据解析失败'
            });
        }

        // 3. 取商户业务字段
        const product_id = data.productId;
        const price = data.price; // 单位分
        const amount = await mapProductToMomoAmount(product_id, pk);
        const currency = data.currency;
        const quantity = data.quantity || 1;

        // ======= 防重放/幂等保障：transactionId只能充值一次 ======
        const [existOrder] = await mysqlQuery(
            'SELECT id FROM transir_recharge_records WHERE transaction_id = ?', [transaction_id]);
        if (existOrder) {
            return res.json({
                code: 0,
                msg: '订单已处理，无需重复入账'
            });
        }

        // 4. 填充充值到期时间，加1年为例
        let expireTimeValue = null;
        {
            // 计算当前到期时间毫秒数（当前时间 + 1年）
            const nowTs = Date.now();
            const plusOneYearTs = new Date(nowTs);
            plusOneYearTs.setFullYear(plusOneYearTs.getFullYear() + 1);
            // 转北京时间字符串
            expireTimeValue = toMySQLDatetimeInBeijing(plusOneYearTs.getTime());
        }

        // 5. 启动事务
        conn = await new Promise((resolve, reject) => {
            poolmomo.getConnection((err, c) => err ? reject(err) : resolve(c));
        });
        await new Promise((resolve, reject) => conn.beginTransaction(err => err ? reject(err) : resolve()));

        // 6. 写入充值记录，所有关键字段都填（amount、transaction_id、product_id、price、pk等）
        const rechargeTime = toMySQLDatetimeInBeijing(data.purchaseDate);
        const envValue = (env === 'sanbox') ? 'sanbox' : null;
        await mysqlQueryWithConn(
            `INSERT INTO transir_recharge_records 
            (user_id, transaction_id, amount, remain, recharge_time, expire_time, status, product_id, price, pk, type, env,created_at)
            VALUES (?, ?, ?, ?, ?, ?, 'unused', ?, ?, ?, ?, ?, ?)`,
            [
                user_id,
                transaction_id,
                amount,
                amount,
                rechargeTime, // 用苹果实际 purchaseDate
                expireTimeValue,
                product_id,
                price,
                pk,
                1,
                envValue,
                utils.formatDate(new Date())
            ],
            conn
        );

        // 7. 更新用户主表余额
        await mysqlQueryWithConn(
            'UPDATE transir_users SET momo_balance = momo_balance + ? WHERE user_id = ?',
            [amount, user_id], conn
        );

        // 8. 查余额，提交
        const [user] = await mysqlQueryWithConn('SELECT momo_balance FROM transir_users WHERE user_id=?', [
            user_id
        ], conn);
        await new Promise((resolve, reject) => conn.commit(err => err ? reject(err) : resolve()));
        conn.release();

        return res.send({
            code: 0,
            msg: 'success',
            data: {
                momo_balance: user ? user.momo_balance : null,
                amount,
                transaction_id,
                product_id,
                price,
                pk,
                expire_time: expireTimeValue,
                currency,
                quantity,
                apple_payload: data
            }
        });

    } catch (err) {
        console.log(err, '充值momo币报错')
        if (conn) {
            try {
                await conn.rollback();
                conn.release();
            } catch (e2) { }
        }
        res.status(500).json({
            code: 500,
            msg: '服务器错误'
        });
    }
});

// 输入：毫秒级时间戳
function toMySQLDatetimeInBeijing(ts) {
    const date = new Date(ts + 8 * 60 * 60 * 1000); // 时间戳加8小时，得到东八区时间
    // 这里不是对Date对象加8小时，而是对毫秒数加8小时（否则Date构造函数按本地时区了，容易混乱）
    return date.toISOString().slice(0, 19).replace('T', ' ');
}

/**
 * 根据产品ID和pk获取momo币到账数量
 * @param {string} product_id 
 * @param {string} pk 平台标识
 * @returns {Promise<number>}
 */
async function mapProductToMomoAmount(product_id, pk) {
    if (pk === 'com.trans.translationAiAndSubtitle') {
        // 数据库查币包规则表，获得total_coins
        const rows = await mysqlQuery(
            'SELECT total_coins FROM transir_product_coin_rules WHERE product_id=? LIMIT 1',
            [product_id]
        );
        if (rows && rows.length) {
            return rows[0].total_coins || 0;
        } else {
            return 0; // 未配置的档位
        }
    } else {
        // 其它渠道（如安卓、web），可自定义映射表
        const productMap = {
            'qrweeknew6': 68,
            'qrweeknew18': 198,
            'qrweeknew36': 388,
            'qrweeknew60': 648,
            'qrweeknew100': 1088,
            'qrweeknew200': 2288,
        };
        return productMap[product_id] || 0;
    }
}

function formatDate(val) {
    if (!val) return '';
    return moment(val).format("YYYY-MM-DD HH:mm:ss");
}

function transRechargeStatus(status) {
    if (status === 'unused') return '未用完';
    if (status === 'usedUp') return '已消耗完';
    if (status === 'expired') return '已过期';
    return status;
}
// POST 分页获取用户充值记录
router.post('/recharge/records', async (req, res) => {
    const {
        user_id,
        page = 1,
        pageSize = 10
    } = req.body;

    if (!user_id) {
        return res.status(400).json({
            code: 400,
            msg: '缺少user_id参数'
        });
    }
    const realPage = Math.max(1, Number(page));
    const realPageSize = Math.max(1, Math.min(Number(pageSize), 100));
    const offset = (realPage - 1) * realPageSize;

    try {
        const totalRowsRes = await mysqlQuery(
            'SELECT COUNT(*) AS total FROM transir_recharge_records WHERE user_id = ?',
            [user_id]
        );
        const total = totalRowsRes[0]?.total || 0;

        let rows = await mysqlQuery(
            `SELECT *
             FROM transir_recharge_records
             WHERE user_id = ?
             ORDER BY recharge_time DESC
             LIMIT ? OFFSET ?`, [user_id, realPageSize, offset]
        );

        // 格式化时间和状态
        rows = rows.map(row => ({
            ...row,
            recharge_time: formatDate(row.recharge_time),
            expire_time: formatDate(row.expire_time),
            created_at: formatDate(row.created_at),
            updated_at: formatDate(row.updated_at),
            status_text: transRechargeStatus(row.status), // 额外加中文状态描述
        }));

        res.send({
            code: 0,
            msg: 'success',
            data: {
                total,
                page: realPage,
                pageSize: realPageSize,
                list: rows
            }
        });
    } catch (e) {
        console.error(e);
        res.status(500).json({
            code: 500,
            msg: '服务器错误'
        });
    }
});
// POST 分页获取用户消费记录
router.post('/consume/records', async (req, res) => {
    const {
        user_id,
        page = 1,
        pageSize = 10
    } = req.body;

    if (!user_id) {
        return res.status(400).json({
            code: 400,
            msg: '缺少user_id参数'
        });
    }
    const realPage = Math.max(1, Number(page));
    const realPageSize = Math.max(1, Math.min(Number(pageSize), 100));
    const offset = (realPage - 1) * realPageSize;

    try {
        const totalRowsRes = await mysqlQuery(
            'SELECT COUNT(*) AS total FROM transir_consume_records WHERE user_id = ?',
            [user_id]
        );
        const total = totalRowsRes[0]?.total || 0;

        let rows = await mysqlQuery(
            `SELECT *
             FROM transir_consume_records
             WHERE user_id = ?
             ORDER BY consume_time DESC
             LIMIT ? OFFSET ?`, [user_id, realPageSize, offset]
        );

        // 格式化各种时间字段
        rows = rows.map(row => ({
            ...row,
            consume_time: formatDate(row.consume_time),
            created_at: formatDate(row.created_at),
            updated_at: formatDate(row.updated_at),
        }));

        res.send({
            code: 0,
            msg: 'success',
            data: {
                total,
                page: realPage,
                pageSize: realPageSize,
                list: rows
            }
        });
    } catch (e) {
        console.error(e);
        res.status(500).json({
            code: 500,
            msg: '服务器错误'
        });
    }
});


async function refreshExpiredMomoRecharge(user_id, conn) {
    await mysqlQueryWithConn(`
        UPDATE transir_recharge_records
        SET status='expired'
        WHERE user_id=? AND status='unused' AND expire_time IS NOT NULL AND expire_time <= NOW() AND remain > 0
    `, [user_id], conn);
}
async function refreshExpiredMomoRecharge1(user_id) {
    await mysqlQuery(`
        UPDATE transir_recharge_records
        SET status='expired'
        WHERE user_id=? AND status='unused' AND expire_time IS NOT NULL AND expire_time <= NOW() AND remain > 0
    `, [user_id]);
}
// 获取币包规则列表（可带分页参数）
router.post('/product-coin-rules/list', async (req, res) => {
    try {
        // 分页数据
        const rows = await mysqlQuery(
            `SELECT * FROM transir_product_coin_rules ORDER BY id ASC;`,
            []
        );
        res.send({
            code: 0,
            msg: 'success',
            data: rows
        });
    } catch (e) {
        console.error('获取币包规则表失败:', e);
        res.status(500).json({
            code: 500,
            msg: '服务器错误'
        });
    }
});
// 编辑（或运营后台编辑）某个档位的基础和赠送币数
router.post('/product-coin-rules/edit', async (req, res) => {
    const {
        product_id,
        base_coins,
        bonus_coins
    } = req.body;
    if (!product_id || base_coins == null || bonus_coins == null) {
        return res.status(400).json({
            code: 400,
            msg: '缺少参数'
        });
    }
    try {
        // 更新币包规则
        const result = await mysqlQuery(
            `UPDATE transir_product_coin_rules 
             SET base_coins=?, bonus_coins=?, updated_at=NOW()
             WHERE product_id=? LIMIT 1`,
            [base_coins, bonus_coins, product_id]
        );
        if (result.affectedRows === 0) {
            return res.status(404).json({
                code: 404,
                msg: 'product_id不存在'
            });
        }
        // 返回最新数据
        const [info] = await mysqlQuery(
            `SELECT * FROM transir_product_coin_rules WHERE product_id=? LIMIT 1`,
            [product_id]
        );
        res.json({
            code: 0,
            msg: '修改成功',
            data: info
        });
    } catch (e) {
        console.error('币包规则修改失败:', e);
        res.status(500).json({
            code: 500,
            msg: '服务器错误'
        });
    }
});


//邀请裂变
// 绑定邀请码接口
router.post('/bindInvitation', async (req, res) => {
    const {
        user_id,
        inviterCode
    } = req.body;
    let conn;
    try {
        // 1. 获取连接，开启事务
        conn = await new Promise((resolve, reject) => {
            poolmomo.getConnection((err, c) => err ? reject(err) : resolve(c));
        });
        await conn.beginTransaction();

        // 2. 查询被邀请人&加锁
        const invitees = await mysqlQueryWithConn(
            'SELECT * FROM transir_users WHERE user_id=? FOR UPDATE',
            [user_id],
            conn
        );
        const invitee = invitees?.[0];
        if (!invitee) {
            await conn.rollback();
            return res.send({
                code: 1,
                msg: '用户不存在'
            });
        }
        if (invitee.invited_by) {
            await conn.rollback();
            return res.send({
                code: 2,
                msg: '已绑定邀请人'
            });
        }

        // 3. 查询邀请人&加锁
        const inviters = await mysqlQueryWithConn(
            'SELECT * FROM transir_users WHERE invitation_code=? FOR UPDATE',
            [inviterCode],
            conn
        );
        const inviter = inviters?.[0];
        if (!inviter) {
            await conn.rollback();
            return res.send({
                code: 3,
                msg: '邀请码无效'
            });
        }
        if (inviter.user_id === user_id) {
            await conn.rollback();
            return res.send({
                code: 4,
                msg: '不能邀请自己'
            });
        }

        // 检查邀请人注册时间早于被邀请人
        if (new Date(inviter.registration_time) >= new Date(invitee.registration_time)) {
            await conn.rollback();
            return res.send({
                code: 5,
                msg: '邀请人注册时间需早于被邀请人'
            });
        }

        // 4. 动态获取奖励天数配置
        const configRows = await mysqlQueryWithConn(
            'SELECT inviter_reward_days, invitee_reward_days FROM transir_system_configs LIMIT 1',
            [],
            conn
        );
        const configRow = configRows && configRows[0] ? configRows[0] : {};
        const inviterRewardDays = typeof configRow.inviter_reward_days === 'number' ?
            configRow.inviter_reward_days :
            parseInt(configRow.inviter_reward_days, 10) || 7;
        let inviteeRewardDays = typeof configRow.invitee_reward_days === 'number' ?
            configRow.invitee_reward_days :
            parseInt(configRow.invitee_reward_days, 10) || 7;

        const specialInviteCodes = ['00PF09'];
        if (specialInviteCodes.includes(inviterCode)) {
            inviteeRewardDays = 30;
        }
        // 5. 【新增】获取会员配额参数，用于直接兑换被邀请人奖励
        const memberConf = await getMemberConfig();
        const now = new Date();

        // 6. 【新增】记录被邀请人的奖励兑换日志
        await mysqlQueryWithConn(
            "INSERT INTO transir_invite_reward_redeem_records (user_id, redeem_days, redeem_type, redeem_time) VALUES (?, ?, ?, ?)",
            [user_id, inviteeRewardDays, 0, now],
            conn
        );

        // 7. 【修改】被邀请人更新：绑定邀请关系 + 直接兑换奖励（根据不同身份处理）
        let inviteeUpdates = ['invited_by=?', 'invitation_time=?'];
        let inviteeParams = [inviter.user_id, now];

        // 根据被邀请人身份进行不同处理
        if (invitee.user_type === 'regular') {
            // 普通用户，直接升级为临时会员
            const expiry = moment(now).add(inviteeRewardDays, 'days').format("YYYY-MM-DD HH:mm:ss");
            inviteeUpdates.push(
                "user_type='temporary_member'",
                "subscription_start_date=?",
                "subscription_expiry_date=?",
                "last_usage_reset_date=?",
                "usage_base_date=?",
                "next_usage_reset_date=?",
                "feature_minutes=?",
                "feature_minutes_left=?",
                "photos=?",
                "photos_left=?",
                "tokens=?",
                "tokens_left=?",
                "float_copy=?",
                "float_copy_left=?",
                "quick_text=?",
                "quick_text_left=?"
            );
            inviteeParams.push(now, expiry, now, now, expiry,
                memberConf.feature_minutes, memberConf.feature_minutes,
                memberConf.photos, memberConf.photos,
                memberConf.tokens, memberConf.tokens,
                memberConf.float_copy, memberConf.float_copy,
                memberConf.quick_text, memberConf.quick_text
            );
        } else if (invitee.user_type === 'temporary_member') {
            // 临时会员，续期
            let baseTime = moment.max(
                moment(now),
                invitee.subscription_expiry_date ? moment(invitee.subscription_expiry_date) : moment(
                    now)
            );
            const expiry = baseTime.add(inviteeRewardDays, 'days').format("YYYY-MM-DD HH:mm:ss");
            inviteeUpdates.push(
                "subscription_expiry_date=?",
                "next_usage_reset_date=?"
            );
            inviteeParams.push(expiry, expiry);
        } else if (invitee.user_type === 'trial_member' || invitee.user_type === 'renewal_member') {
            // 体验/续费会员，奖励暂存，等待会员到期自动激活
            inviteeUpdates.push("post_vip_pending_days=post_vip_pending_days+?");
            inviteeParams.push(inviteeRewardDays);
        }

        // 【修改】公用部分：兑换奖励相关字段更新
        inviteeUpdates.push("invite_reward_redeemed_days=invite_reward_redeemed_days+?");
        inviteeParams.push(inviteeRewardDays);

        // 执行被邀请人更新
        inviteeParams.push(user_id);
        await mysqlQueryWithConn(
            `UPDATE transir_users SET ${inviteeUpdates.join(',')} WHERE user_id=?`,
            inviteeParams,
            conn
        );

        // 8. 邀请人更新：增加邀请次数 + 增加待兑换奖励天数（不直接兑换）
        await mysqlQueryWithConn(
            'UPDATE transir_users SET invited_count=invited_count+1, invite_reward_pending_days=invite_reward_pending_days+? WHERE user_id=?',
            [inviterRewardDays, inviter.user_id],
            conn
        );

        // 9. 记录邀请行为
        await mysqlQueryWithConn(
            'INSERT INTO transir_invitation_records (inviter_user_id, invitee_user_id, invitee_username, invitee_avatar_url) VALUES (?,?,?,?)',
            [inviter.user_id, invitee.user_id, invitee.username, invitee.avatar_url],
            conn
        );

        // 10. 提交事务
        await conn.commit();
        return res.send({
            code: 0,
            msg: '绑定成功，奖励已自动激活',
            inviteeRewardDays
        });
    } catch (e) {
        if (conn) await conn.rollback();
        return res.send({
            code: 500,
            msg: e.message || '服务器错误'
        });
    } finally {
        if (conn) conn.release();
    }
});



// 兑换邀请奖励接口
router.post('/redeemInviteReward', async (req, res) => {
    const {
        user_id
    } = req.body;
    let conn;
    try {
        // 1. 获取连接，开启事务
        conn = await new Promise((resolve, reject) => {
            poolmomo.getConnection((err, c) => err ? reject(err) : resolve(c));
        });
        await conn.beginTransaction();

        // 2. 查询并加锁
        const users = await mysqlQueryWithConn("SELECT * FROM transir_users WHERE user_id=? FOR UPDATE", [
            user_id
        ], conn);
        const user = users?.[0];
        if (!user) {
            await conn.rollback();
            return res.send({
                code: 1,
                msg: '用户不存在'
            });
        }
        if (user.invite_reward_pending_days < 1) {
            await conn.rollback();
            return res.send({
                code: 2,
                msg: '暂无可兑换的邀请奖励天数'
            });
        }
        const redeemDays = user.invite_reward_pending_days;
        const now = new Date();

        // 3. 获取会员配额参数（根据你自己的工具函数实现）
        const memberConf = await getMemberConfig();

        // 4. 记录兑换日志
        await mysqlQueryWithConn(
            "INSERT INTO transir_invite_reward_redeem_records (user_id, redeem_days, redeem_type, redeem_time) VALUES (?, ?, ?, ?)",
            [user_id, redeemDays, 1, now],
            conn
        );

        // 5. 判断类型，构造更新
        let updates = [];
        let params = [];
        if (user.user_type === 'regular') {
            // 普通用户直接升级为临时会员
            const nowStr = moment(now).format("YYYY-MM-DD HH:mm:ss");
            const expiry = moment(now).add(redeemDays, 'days').format("YYYY-MM-DD HH:mm:ss");

            // 新增: 判断是否大于等于一个月，决定 next_usage_reset_date 设置
            let nextUsageResetDate;
            if (redeemDays >= 30) {
                // 超过or等于30天，自然月
                nextUsageResetDate = moment(now).add(1, 'month').format("YYYY-MM-DD HH:mm:ss");
            } else {
                nextUsageResetDate = expiry;
            }

            updates.push(
                "user_type='temporary_member'",
                "subscription_start_date=?",
                "subscription_expiry_date=?",
                "last_usage_reset_date=?",
                "usage_base_date=?",
                "next_usage_reset_date=?",
                "feature_minutes=?",
                "feature_minutes_left=?",
                "photos=?",
                "photos_left=?",
                "tokens=?",
                "tokens_left=?",
                "float_copy=?",
                "float_copy_left=?",
                "quick_text=?",
                "quick_text_left=?"
            );
            params.push(
                nowStr, expiry, nowStr, nowStr, nextUsageResetDate,
                memberConf.feature_minutes, memberConf.feature_minutes,
                memberConf.photos, memberConf.photos,
                memberConf.tokens, memberConf.tokens,
                memberConf.float_copy, memberConf.float_copy,
                memberConf.quick_text, memberConf.quick_text
            );
        } else if (user.user_type === 'temporary_member') {
            // 临时会员，针对原到期日
            let baseTime = moment.max(
                moment(now),
                user.subscription_expiry_date ? moment(user.subscription_expiry_date) : moment(now)
            );
            const expiry = baseTime.add(redeemDays, 'days').format("YYYY-MM-DD HH:mm:ss");
            // 新增: 判断新增天数是否 >=30，决定 next_usage_reset_date
            let nextUsageResetDate;
            if (redeemDays >= 30) {
                // 注意：这里按当前时间加1个月
                nextUsageResetDate = moment(now).add(1, 'month').format("YYYY-MM-DD HH:mm:ss");
            } else {
                nextUsageResetDate = expiry;
            }

            updates.push(
                "subscription_expiry_date=?",
                "next_usage_reset_date=?"
            );
            params.push(expiry, nextUsageResetDate);
        } else if (user.user_type === 'trial_member' || user.user_type === 'renewal_member') {
            // 体验/续费会员，奖励暂存，等待会员到期自动激活
            updates.push("post_vip_pending_days=post_vip_pending_days+?");
            params.push(redeemDays);
        }
        // 公用部分
        updates.push("invite_reward_pending_days=0",
            "invite_reward_redeemed_days=invite_reward_redeemed_days+?");
        params.push(redeemDays);

        // 6. 执行用户主表更新
        await mysqlQueryWithConn(
            `UPDATE transir_users SET ${updates.join(',')} WHERE user_id=?`,
            [...params, user_id],
            conn
        );

        // 7. 提交事务
        await conn.commit();
        return res.send({
            code: 0,
            msg: '兑换成功',
            redeemDays
        });
    } catch (e) {
        if (conn) await conn.rollback();
        return res.send({
            code: 500,
            msg: e.message || '服务器错误'
        });
    } finally {
        if (conn) conn.release();
    }
});
//邀请记录列表
router.post('/getInvitationRecords', async (req, res) => {
    let {
        user_id,
        page = 1,
        size = 10
    } = req.body;
    page = parseInt(page, 10) || 1;
    size = parseInt(size, 10) || 20;
    const offset = (page - 1) * size;

    try {
        // 1. 获取总数
        const countRows = await mysqlQuery(
            'SELECT COUNT(*) as total FROM transir_invitation_records WHERE inviter_user_id = ? ',
            [user_id]
        );
        const total = countRows[0]?.total || 0;

        // 2. 获取数据列表（可根据需要只查自己发起作为邀请人的，或做出入参区分）
        const records = await mysqlQuery(
            `SELECT * FROM transir_invitation_records
             WHERE inviter_user_id = ?
             ORDER BY invite_time DESC
             LIMIT ? OFFSET ?`,
            [user_id, size, offset]
        );

        res.send({
            code: 0,
            msg: 'success',
            data: {
                list: records,
                total,
                page,
                size
            }
        });
    } catch (e) {
        res.send({
            code: 500,
            msg: e.message || '服务器错误'
        });
    }
});
//邀请奖励兑换记录列表
router.post('/getInviteRewardRedeemRecords', async (req, res) => {
    let {
        user_id,
        page = 1,
        size = 10
    } = req.body;
    page = parseInt(page, 10) || 1;
    size = parseInt(size, 10) || 20;
    const offset = (page - 1) * size;

    try {
        // 1. 获取总数
        const countRows = await mysqlQuery(
            'SELECT COUNT(*) as total FROM transir_invite_reward_redeem_records WHERE user_id = ?',
            [user_id]
        );
        const total = countRows[0]?.total || 0;

        // 2. 获取数据列表
        const records = await mysqlQuery(
            `SELECT * FROM transir_invite_reward_redeem_records
             WHERE user_id = ?
             ORDER BY redeem_time DESC
             LIMIT ? OFFSET ?`,
            [user_id, size, offset]
        );

        res.send({
            code: 0,
            msg: 'success',
            data: {
                list: records,
                total,
                page,
                size
            }
        });
    } catch (e) {
        res.send({
            code: 500,
            msg: e.message || '服务器错误'
        });
    }
});
//给用户重置为普通用户
router.post('/putong', async (req, res) => {
    console.log('进啦')
    const { user_id } = req.body
    // 2. 查询用户
    try {
        const sql = 'SELECT * FROM transir_users WHERE user_id = ? LIMIT 1';
        const result = await mysqlQuery(sql, [user_id]);
        if (result.length === 0) {
            return res.status(202).json({
                code: 202,
                message: '用户不存在'
            });
        }
        let user = result[0];
        // 拉取普通用户（regular）的用量参数配置
        const configSql = `
                    SELECT 
                        regular_feature_minutes AS feature_minutes, 
                        regular_photos_limit AS photos,
                        regular_tokens_limit AS tokens,
                        regular_float_copy_limit AS float_copy,
                        regular_quick_text_limit AS quick_text
                    FROM transir_system_configs 
                    LIMIT 1
                `;
        const [config] = await mysqlQuery(configSql, []);

        // 解构出各项配置
        const {
            feature_minutes,
            photos,          // 这个就是 regular_photos_limit
            tokens,
            float_copy,
            quick_text
        } = config;

        // 记录普通用户照片配额
        const regular_photos_limit = config.photos || 0;

        // 拿用户历史使用数（你表里 regular_photos_used 字段记录的是普通用户时的已用数量）
        const regular_photos_used = user.regular_photos_used || 0;

        // 计算剩余配额，最少为 0
        const photos_left = Math.max(0, regular_photos_limit - regular_photos_used);
        const subscriptionExpiryDate = user.subscription_expiry_date ?
            moment(user.subscription_expiry_date).tz("Asia/Shanghai").valueOf() :
            null;
        // last_usage_reset_date 用最后订阅过期时间
        const lastUsageResetDate = moment(subscriptionExpiryDate)
            .tz("Asia/Shanghai")
            .format("YYYY-MM-DD HH:mm:ss");

        // next_usage_reset_date 用过期时间+1个月（给下一周期普通配额计算用）
        const nextResetDate = moment(subscriptionExpiryDate)
            .tz("Asia/Shanghai")
            .add(1, 'month')
            .format("YYYY-MM-DD HH:mm:ss");

        // 为什么更新 photos/used/left 是这样逻辑？
        //   1. photos: 一定要设置为普通用户最大配额，即config.regular_photos_limit。
        //   2. photos_used: 一定用 regular_photos_used（存普通模式下历史已用数）。
        //   3. photos_left:    = photos - photos_used，最少为 0。

        await mysqlQuery(
            `UPDATE transir_users SET
                        user_type = 'regular',
                        subscription_expiry_date = NULL,
                        feature_minutes         = ?,
                        feature_minutes_used    = 0,
                        feature_minutes_left    = ?,
                        photos                  = ?,
                        photos_used             = ?,
                        photos_left             = ?,
                        tokens                  = ?,
                        tokens_used             = 0,
                        tokens_left             = ?,
                        float_copy              = ?,
                        float_copy_used         = 0,
                        float_copy_left         = ?,
                        quick_text              = ?,
                        quick_text_used         = 0,
                        quick_text_left         = ?,
                        last_usage_reset_date   = ?,
                        next_usage_reset_date   = ?
                     WHERE user_id = ?`,
            [
                feature_minutes,         // 普通精翻分钟配额
                feature_minutes,         // 剩余精翻分钟（初始等于配额）
                regular_photos_limit,    // 照片总配额（普通）
                regular_photos_used,     // 普通已用照片数 (regular_photos_used 字段)
                photos_left,             // 普通剩余照片数
                tokens,                  // 普通 tokens 配额
                tokens,                  // 剩余 tokens
                float_copy,              // 普通悬浮复制配额
                float_copy,              // 剩余悬浮复制
                quick_text,              // 普通快捷文字配额
                quick_text,              // 剩余快捷文字
                lastUsageResetDate,
                nextResetDate,
                user.user_id
            ]
        );
    } catch (error) {
        console.log(error)
    }
     
     
    res.send({code:0})
})

//活动列表接口
// 获取活动分页列表
// POST /getActivityList
// 入参：{ page: 1, pageSize: 20 }（缺省页数和每页数量默认为1和20）
router.post('/getActivityList', async (req, res) => {
    let {
        page = 1, size = 10, name = '', status = ''
    } = req.body;
    page = parseInt(page, 10) || 1;
    size = parseInt(size, 10) || 10;
    const offset = (page - 1) * size;

    // 动态组装SQL
    let where = ['logic_status=1'];
    let params = [];

    if (name && typeof name === 'string') {
        where.push('name LIKE ?');
        params.push('%' + name + '%');
    }
    if (status !== '' && status !== null && status !== undefined) {
        where.push('status = ?');
        params.push(Number(status));
    }

    const whereStr = where.length ? 'WHERE ' + where.join(' AND ') : '';

    try {
        // 总数
        const countSql = `SELECT COUNT(*) AS total FROM transir_activity ${whereStr}`;
        const totalRows = await mysqlQuery(countSql, params);
        const total = totalRows[0]?.total || 0;

        // 数据列表
        const listSql = `SELECT * FROM transir_activity ${whereStr} ORDER BY id DESC LIMIT ? OFFSET ?`;
        const list = await mysqlQuery(listSql, [...params, size, offset]);

        res.send({
            code: 0,
            msg: 'success',
            data: {
                list,
                total,
                page,
                size
            }
        });
    } catch (e) {
        res.send({
            code: 500,
            msg: e.message || '服务器错误'
        });
    }
});

// 启用/停用活动（status）
// POST /updateActivityStatus
// 入参：{ id, status }（status=1启用, 0停用）
router.post('/updateActivityStatus', async (req, res) => {
    const {
        id,
        status
    } = req.body;
    if (!id || (status !== 0 && status !== 1 && status !== "0" && status !== "1")) {
        return res.send({
            code: 1,
            msg: '参数错误'
        });
    }
    try {
        await mysqlQuery("UPDATE transir_activity SET status=? WHERE id=?", [status, id]);
        res.send({
            code: 0,
            msg: '修改成功'
        });
    } catch (e) {
        res.send({
            code: 500,
            msg: e.message || '服务器错误'
        });
    }
});
router.post('/updateActivityEnv_type', async (req, res) => {
    const {
        id,
        status
    } = req.body;

    try {
        await mysqlQuery("UPDATE transir_activity SET env_type=? WHERE id=?", [status, id]);
        res.send({
            code: 0,
            msg: '修改成功'
        });
    } catch (e) {
        res.send({
            code: 500,
            msg: e.message || '服务器错误'
        });
    }
});
// 设置活动逻辑状态 logic_status
// POST /updateActivityLogicStatus
// 入参：{ id, logic_status }（1正常 0删除/软删除）
router.post('/updateActivityLogicStatus', async (req, res) => {
    const {
        id,
        logic_status
    } = req.body;
    if (!id || (logic_status !== 0 && logic_status !== 1 && logic_status !== "0" && logic_status !== "1")) {
        return res.send({
            code: 1,
            msg: '参数错误'
        });
    }
    try {
        await mysqlQuery("UPDATE transir_activity SET logic_status=? WHERE id=?", [logic_status, id]);
        res.send({
            code: 0,
            msg: '修改成功'
        });
    } catch (e) {
        res.send({
            code: 500,
            msg: e.message || '服务器错误'
        });
    }
});

router.post('/saveActivity', async (req, res) => {
    let {
        id, // 活动id，编辑时必须，新增时可不传
        name,
        start_time,
        end_time,
        status, // 不要默认1
        logic_status = 1,
        entry_list,
        env_type,   // 新增
        min_version,// 新增
        max_version,// 新增
    } = req.body;

    if (!name || !start_time || !end_time || !Array.isArray(entry_list) || !entry_list.length) {
        return res.send({
            code: 1,
            msg: '参数不完整'
        });
    }
    if (env_type == '' || !env_type) {
        env_type = 'dev'
    }
    // 自动拼 display_language
    const allLangs = Array.from(new Set(entry_list.map(item => item.lang))).filter(Boolean);
    const display_language = allLangs.join(',');

    // 转存 entry_list 为 JSON 字符串
    const entryListStr = JSON.stringify(entry_list);

    try {
        if (!id) {
            // === 新增 ===
            const saveStatus = (typeof status === 'number' || status === '0' || status === '1') ?
                Number(status) :
                0; // 新增默认为 0（停用），前端不传或传空都按 0
            await mysqlQuery(
                `INSERT INTO transir_activity 
                    (name, env_type, min_version, max_version, start_time, end_time, status, logic_status, entry_list, display_language, created_at, updated_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
                [name, env_type, min_version, max_version, start_time, end_time, saveStatus, logic_status, entryListStr, display_language]
            );
            return res.send({
                code: 0,
                msg: '新增成功(默认停用)'
            });
        } else {
            // === 编辑 ===
            // status 取传参/旧值都可以
            const editStatus = (typeof status === 'number' || status === '0' || status === '1') ?
                Number(status) :
                1; // 没传按1启用
            await mysqlQuery(
                `UPDATE transir_activity 
                     SET name=?, env_type=?, min_version=?, max_version=?, start_time=?, end_time=?, status=?, logic_status=?, entry_list=?, display_language=?, updated_at=NOW() 
                     WHERE id=?`,
                [name, env_type, min_version, max_version, start_time, end_time, editStatus, logic_status, entryListStr, display_language, id]
            );
            return res.send({
                code: 0,
                msg: '编辑成功'
            });
        }
    } catch (e) {
        res.send({
            code: 500,
            msg: e.message || '服务器错误'
        });
    }
});
function normalizeVersion(version) {
    if (typeof version !== 'string') throw new Error('版本号必须为字符串');
    const cleanVer = version.replace(/^v/i, '');
    const parts = cleanVer.split('.').slice(0, 3);
    if (parts.some(p => !/^\d+$/.test(p))) {
        throw new Error(`非法版本号格式: ${version}`);
    }
    while (parts.length < 3) parts.push('0');
    return parts.map(p => p.padStart(6, '0')).join('.');
}
// 客户端获取生效中的活动列表
// POST /getOnlineActivityList
router.post('/getOnlineActivityList', async (req, res) => {
    const { package_name, version } = req.body;
    let env_type = 'prod';
    if (package_name === 'com.trans.translationAiAndSubtitle') {
        env_type = 'prod';
    } else if (package_name === 'com.TranslationAISubTitle') {
        env_type = 'dev';
    } else {
        // 其余包名默认prod或可按需处理
        env_type = 'prod';
    }
    try {
        // 获取当前上海时间
        const now = moment().tz('Asia/Shanghai').format('YYYY-MM-DD HH:mm:ss');

        // // 查询条件：
        // const list = await mysqlQuery(
        //     `SELECT * FROM transir_activity
        //      WHERE logic_status=1
        //        AND status=1
        //        AND start_time <= ?
        //        AND end_time >= ?
        //      ORDER BY id DESC`,
        //     [now, now]
        // );
        // 2. 查询活动表
        const sql = `
         SELECT * FROM transir_activity
         WHERE logic_status = 1
           AND status = 1
           AND start_time <= ?
           AND end_time >= ?
           AND env_type = ?
         ORDER BY id DESC
     `;
        const activityList = await mysqlQuery(sql, [now, now, env_type]);
        // console.log(activityList)
        // 3. 判断version是否有效，只有有效再做版本过滤，否则不处理
        let finalList = [];
        const hasVersion = version !== undefined && version !== null && version !== '';
        let userVer = '';
        if (Array.isArray(activityList) && activityList.length > 0) {
            if (hasVersion) {

                // 仅当version有效时才做normalize和过滤
                try {
                    userVer = normalizeVersion(version);
                } catch (e) {
                    // version格式非法则全部不过滤也可直接返回错误，看业务需要
                    userVer = '';
                }
            }
            finalList = activityList.filter(item => {
                // 不传version则不过滤直接通过
                if (!hasVersion) return true;
                // 下面的只在 version 有效时生效
                let ok = true;
                if (item.min_version) {
                    try {
                        let minV = normalizeVersion(item.min_version);
                        if (userVer < minV) ok = false;
                    } catch (e) { }
                }
                if (item.max_version) {
                    try {
                        let maxV = normalizeVersion(item.max_version);

                        if (userVer > maxV) ok = false;
                    } catch (e) { }
                }
                return ok;
            });
        }

        // 4. entry_list 转数组，reward不经过任何版本筛选
        let showList = [];
        if (finalList.length > 0) {
            showList = finalList.map(item => {
                let entryArr = [];
                if (typeof item.entry_list === 'string') {
                    try {
                        entryArr = JSON.parse(item.entry_list) || [];
                    } catch (e) {
                        entryArr = [];
                    }
                } else if (Array.isArray(item.entry_list)) {
                    entryArr = item.entry_list;
                } else if (typeof item.entry_list === 'object' && item.entry_list !== null) {
                    entryArr = [item.entry_list];
                }
                return {
                    ...item,
                    entry_list: entryArr
                };
            });
        }

        res.send({
            code: 0,
            msg: 'success',
            data: showList
        });
    } catch (e) {
        res.send({
            code: 500,
            msg: e.message || '服务器错误'
        });
    }
});
router.post('/getInvitationCodeByUsername', async (req, res) => {
    const {
        user_id
    } = req.body;
    if (!user_id) {
        return res.send({
            code: 400,
            msg: "缺少 user_id 参数"
        });
    }

    try {
        // 查询用户邀请码
        const sql = "SELECT invitation_code FROM transir_users WHERE user_id = ? LIMIT 1";
        const result = await mysqlQuery(sql, [user_id]);
        if (!result || result.length === 0) {
            return res.send({
                code: 404,
                msg: "用户不存在或无邀请码"
            });
        }
        res.send({
            code: 0,
            msg: "success",
            invitation_code: result[0].invitation_code
        });
    } catch (e) {
        res.send({
            code: 500,
            msg: e.message || "服务器错误"
        });
    }
});


router.post('/updateCountry', async (req, res) => {
    const { user_id, country } = req.body;
    if (!user_id) {
        return res.status(400).json({ code: 400, msg: '缺少 user_id 参数' });
    }
    if (!country) {
        return res.status(400).json({ code: 400, msg: '缺少 country 参数' });
    }

    try {
        const sql = 'UPDATE transir_users SET country = ? WHERE user_id = ?';
        // 这里假定你用 poolmomo，如果是其它库请调整
        const result = await new Promise((resolve, reject) => {
            poolmomo.query(sql, [country, user_id], (err, res) => {
                if (err) return reject(err);
                resolve(res);
            });
        });

        if (result.affectedRows < 1) {
            return res.json({ code: 202, msg: '用户不存在或未更新' });
        }
        res.json({ code: 0, msg: '国家更新成功' });
    } catch (err) {
        console.error('更新国家失败:', err);
        res.status(500).json({ code: 500, msg: '国家更新失败', error: err.message });
    }
});


//活动相关
// 用户奖励申请接口
// 路由: POST /api/user/reward/apply
// 参数: activity_id, source, link
router.post('/reward/apply', async (req, res) => {
    const {
        user_id,
        activity_id,
        activity_name,
        source,
        link,
        username
    } = req.body;

    // 参数基础校验
    if (!activity_id || !source || !link) {
        return res.status(400).json({
            code: 400,
            msg: '缺少必要参数'
        });
    }


    // 插入用户申请记录
    const insertSql = `
        INSERT INTO transir_user_reward_apply
        (user_id,username, activity_id, activity_name, source, link, status, apply_time, created_at, updated_at)
        SELECT ?, ?,?, ?, ?, ?, 1, NOW(), NOW(), NOW()
        FROM transir_activity
        WHERE id = ?
    `;
    // 使用 select...insert 可以把活动名写入申请表，防止活动后续被删改
    await mysqlQuery(insertSql, [user_id, username, activity_id, activity_name, source, link, activity_id]);

    res.send({
        code: 0,
        msg: '申请成功，等待审核'
    });
})
router.post('/reward/apply2', async (req, res) => {
    const {
        user_id,
        activity_id,
        activity_name,
        source,
        link,
        file,
        username
    } = req.body;

    // 参数基础校验
    if (!activity_id || !source || !link) {
        return res.status(400).json({
            code: 400,
            msg: '缺少必要参数'
        });
    }


    // 插入用户申请记录
    const insertSql = `
        INSERT INTO transir_user_reward_apply
        (user_id,username, activity_id, activity_name, source, link,file, status, apply_time, created_at, updated_at)
        SELECT ?, ?,?,?, ?, ?, ?, 1, NOW(), NOW(), NOW()
        FROM transir_activity
        WHERE id = ?
    `;
    // 使用 select...insert 可以把活动名写入申请表，防止活动后续被删改
    await mysqlQuery(insertSql, [user_id, username, activity_id, activity_name, source, link, file, activity_id]);

    res.send({
        code: 0,
        msg: '申请成功，等待审核'
    });
})
//获取申请列表接口

router.post('/reward/apply/list', async (req, res) => {
    let {
        page = 1, size = 10, activity_name, source, status, user_id
    } = req.body;

    page = Number(page) > 0 ? Number(page) : 1;
    size = Number(size) > 0 ? Number(size) : 10;

    // 动态SQL拼接（多查两个字段，子查询方式实现）
    let sql = `
        SELECT 
            *,
            (
                SELECT COUNT(*)
                FROM transir_user_reward_apply t2
                WHERE t2.user_id = transir_user_reward_apply.user_id
                  AND t2.activity_id = transir_user_reward_apply.activity_id
            ) AS apply_count,
            (
                SELECT GROUP_CONCAT(rewards)
                FROM transir_user_reward_apply t3
                WHERE t3.user_id = transir_user_reward_apply.user_id
                  AND t3.activity_id = transir_user_reward_apply.activity_id
                  AND t3.link = transir_user_reward_apply.link
                  AND t3.status=2
                  AND t3.rewards IS NOT NULL
            ) AS granted_rewards
        FROM transir_user_reward_apply
        WHERE 1 = 1
    `;

    let params = [];

    // 别忘了业务筛选，拼接参数
    if (activity_name) {
        sql += ' AND activity_name LIKE ? ';
        params.push(`%${activity_name}%`);
    }
    if (source) {
        sql += ' AND source = ? ';
        params.push(source);
    }
    if (status) {
        sql += ' AND status = ? ';
        params.push(status);
    }
    if (user_id) {
        sql += ' AND user_id = ? ';
        params.push(user_id);
    }

    sql += ' ORDER BY apply_time DESC ';
    sql += ' LIMIT ? OFFSET ? ';
    params.push(size, (page - 1) * size);

    try {
        const list = await mysqlQuery(sql, params);

        // 兼容 rewards 反序列化 & 整理 granted_rewards 字段
        list.forEach(row => {
            // 格式化rewards
            if (row.rewards && typeof row.rewards === 'string') {
                try {
                    row.rewards = JSON.parse(row.rewards)
                } catch (e) {
                    row.rewards = []
                }
            }
            // granted_rewards是所有已发放奖励的json字符串，用逗号拼接
            // 需要解析成数组，和普通 rewards 保持一致风格
            if (row.granted_rewards) {
                // 有多个奖励json字符串用逗号拼接，先分割再解析
                // 注意数据库 GROUP_CONCAT 默认逗号分隔，如果json里有逗号会混乱，建议 reward 结构简单
                row.granted_rewards_list = [];
                row.granted_rewards_list = parseGroupConcatRewards(row.granted_rewards);
            } else {
                row.granted_rewards_list = [];
            }
        });

        // 查询总数的SQL不变
        let countSql = `
            SELECT COUNT(1) as total
            FROM transir_user_reward_apply
            WHERE 1 = 1
        `;
        let countParams = [];
        if (activity_name) {
            countSql += ' AND activity_name LIKE ? ';
            countParams.push(`%${activity_name}%`);
        }
        if (source) {
            countSql += ' AND source = ? ';
            countParams.push(source);
        }
        if (status) {
            countSql += ' AND status = ? ';
            countParams.push(status);
        }
        if (user_id) {
            countSql += ' AND user_id = ? ';
            countParams.push(user_id);
        }
        const countRows = await mysqlQuery(countSql, countParams);
        const total = countRows[0]?.total || 0;

        res.send({
            code: 0,
            msg: 'ok',
            data: {
                total,
                list,
                page,
                size,
            },
        });
    } catch (err) {
        res.status(500).json({
            code: 500,
            msg: '查询失败: ' + err.message,
        });
    }
});

function parseGroupConcatRewards(grantedRewardsStr) {
    if (!grantedRewardsStr) return [];
    // 匹配所有形如 [xxxx] 这样的json数组，并parse并合并
    let arr = [];
    let reg = /\[[^\[\]]*?\]/g;
    let match;
    while ((match = reg.exec(grantedRewardsStr))) {
        try {
            let part = match[0];
            let val = JSON.parse(part);
            if (Array.isArray(val)) arr.push(...val);
        } catch { }
    }
    return arr;
}

// 审核奖励申请接口
// 路由: POST /reward/apply/audit
// 参数：id（申请id），status（2=通过，3=拒绝），reason（原因，非必填），rewards（奖励json数组，通过时用）
router.post('/reward/apply/audit', async (req, res) => {
    // 解构参数
    const {
        id,
        status,
        reason,
        rewards
    } = req.body;

    // 1. 参数校验：id合法，并且status值2或3（2为通过，3为拒绝）
    if (!id || ![2, 3].includes(Number(status))) {
        return res.status(400).json({
            code: 400,
            msg: '参数错误：id和status必填'
        });
    }

    // 2. 先查数据库里该申请记录是否存在（防止乱审、重复审）
    let applyRows;
    try {
        applyRows = await mysqlQuery('SELECT * FROM transir_user_reward_apply WHERE id = ?', [id]);
    } catch (err) {
        return res.status(500).json({
            code: 500,
            msg: '数据库查询异常: ' + err.message
        });
    }
    if (applyRows.length === 0) {
        return res.status(404).json({
            code: 404,
            msg: '申请不存在'
        });
    }
    const apply = applyRows[0];
    if (apply.status !== 1) {
        return res.json({
            code: 409,
            msg: '该申请已审核，不可重复操作'
        });
    }
    let conn;
    try {
        // 3.1 获取连接并开启事务
        conn = await new Promise((resolve, reject) => poolmomo.getConnection((err, connection) => err ?
            reject(err) : resolve(connection)));
        await new Promise((resolve, reject) => conn.beginTransaction(err => err ? reject(err) : resolve()));

        // 3.2 审核记录状态、原因与奖励json更新（奖励需字符串化）
        await mysqlQueryWithConn(
            `UPDATE transir_user_reward_apply
             SET status=?, reason=?, rewards=?, audit_time=NOW(), updated_at=NOW()
             WHERE id=?`,
            [
                status,
                reason || '',
                rewards ? JSON.stringify(rewards) : null,
                id
            ], conn
        );

        // 3.3 如审核通过，需要真正发奖励
        if (Number(status) === 2 && rewards) {
            // rewardList支持字符串和对象两种场景
            let rewardList = typeof rewards === 'string' ? JSON.parse(rewards) : rewards;
            for (let reward of rewardList) {
                // 只处理momo_amount大于0的奖励
                if (reward.momo_amount && Number(reward.momo_amount) > 0) {
                    // 插入充值记录
                    // 1. 计算到期时间：当前时间+1年
                    let expireTimeValue = null;
                    {
                        const now = new Date();
                        now.setFullYear(now.getFullYear() + 1);
                        // 加8小时，单位ms
                        now.setTime(now.getTime() + 8 * 60 * 60 * 1000);
                        // 转换为 'YYYY-MM-DD HH:mm:ss' 格式
                        expireTimeValue = now.toISOString().slice(0, 19).replace('T', ' ');
                    }

                    // 2. 插入奖励币记录到 transir_recharge_records
                    await mysqlQueryWithConn(
                        `INSERT INTO transir_recharge_records
        (user_id, amount, remain, product_id, recharge_time, expire_time, status, type)
     VALUES (?, ?, ?, ?, NOW(), ?, 'unused', 3)`,
                        [
                            apply.user_id, // 用户ID
                            reward.momo_amount, // 获得币数
                            reward.momo_amount, // remain同初始数
                            null, // 明确标记此奖励
                            expireTimeValue // 用JS计算好的到期时间
                        ],
                        conn
                    );
                    // 同步更新用户表余额
                    await mysqlQueryWithConn(
                        `UPDATE transir_users SET momo_balance = momo_balance + ? WHERE user_id = ?`,
                        [reward.momo_amount, apply.user_id],
                        conn
                    );

                }
                // 此处可拓展发其他奖励
            }
        }

        // 3.4 一切正常，提交事务
        await new Promise((resolve, reject) => conn.commit(err => err ? reject(err) : resolve()));
        conn.release();

        // 4. 返回审核ok
        res.json({
            code: 0,
            msg: '审核完成'
        });
    } catch (err) {
        // 事务异常时回滚，防止错奖，同时释放连接
        if (conn) {
            try {
                await new Promise(r => conn.rollback(() => r()));
                conn.release();
            } catch (e) { }
        }
        res.status(500).json({
            code: 500,
            msg: '审核处理失败: ' + err.message,
        });
    }
});

// 获取指定活动指定语言的奖励结构
// 路由: POST /activity/reward_options
// 参数：activity_id（活动ID），lang（语言代码），lang不传默认为'zh'
router.post('/activity/reward_options', async (req, res) => {
    // 拿参数
    const {
        activity_id,
        lang = 'zh'
    } = req.body;

    // 参数校验
    if (!activity_id) {
        return res.status(400).json({
            code: 400,
            msg: '缺少activity_id参数'
        });
    }

    // 查询活动表，只查启用/未删除的
    const actRows = await mysqlQuery(
        // 'SELECT * FROM transir_activity WHERE id = ? AND logic_status = 1 AND status = 1',
        'SELECT * FROM transir_activity WHERE id = ?',
        [activity_id]
    );
    if (actRows.length === 0) {
        return res.status(404).json({
            code: 404,
            msg: '活动不存在或已停用'
        });
    }

    // entry_list字段：json字符串结构
    let entryList = [];
    try {
        entryList = JSON.parse(actRows[0].entry_list || '[]');
    } catch (err) {
        // 解析失败，返回空
        return res.json({
            code: 0,
            data: []
        });
    }

    // 查找指定lang的奖励
    const entry = entryList.find(e => e.lang === lang);
    // 返回奖励数组或空
    res.send({
        code: 0,
        data: entry && entry.rewards ? entry.rewards : []
    });
});
router.post('/activity/reward_options_param', async (req, res) => {
    // 拿参数
    const {
        activity_id,
        lang = 'zh'
    } = req.body;

    // 参数校验
    if (!activity_id) {
        return res.status(400).json({
            code: 400,
            msg: '缺少activity_id参数'
        });
    }

    // 查询活动表，只查启用/未删除的
    const actRows = await mysqlQuery(
        // 'SELECT * FROM transir_activity WHERE id = ? AND logic_status = 1 AND status = 1',
        'SELECT * FROM transir_activity WHERE id = ?',
        [activity_id]
    );
    if (actRows.length === 0) {
        return res.status(404).json({
            code: 404,
            msg: '活动不存在或已停用'
        });
    }

    // entry_list字段：json字符串结构
    let entryList = [];
    try {
        entryList = JSON.parse(actRows[0].entry_list || '[]');
    } catch (err) {
        // 解析失败，返回空
        return res.json({
            code: 0,
            data: []
        });
    }

    // 查找指定lang的奖励
    const entry = entryList.find(e => e.lang === lang);
    // 返回奖励数组或空
    res.send({
        code: 0,
        data: entry
    });
});

/**
 * 查询用户所有未通知的奖励记录
 * @param {string} userId
 * @returns {Promise<Array>} 奖励记录数组
 */
async function queryUserUnnotifiedRewards(userId) {
    const sql = `
        SELECT id, remain 
        FROM transir_recharge_records
        WHERE user_id = ?
          AND type IN ('2','3')
          AND recharge_time > '2025-07-04'
          AND notified = 0
    `;
    return await mysqlQuery(sql, [userId]);
}

/**
 * 批量标记为已通知
 * @param {Array} ids 奖励记录ID数组
 */
async function markRewardsAsNotified(ids) {
    if (!ids.length) return;
    const sql = `
        UPDATE transir_recharge_records SET notified = 1
        WHERE id IN (${ids.map(() => '?').join(',')})
    `;
    await mysqlQuery(sql, ids);
}

// 主路由
// 主业务路由
router.post('/user/rewards/unread', async (req, res) => {
    try {
        const user_id = req.body.user_id;
        if (!user_id) {
            return res.json({
                code: 400,
                msg: '缺少user_id参数'
            });
        }
        // 查询用户所有未通知的奖励
        const rewards = await queryUserUnnotifiedRewards(user_id);
        let total_remain = 0;
        const ids = [];
        for (const r of rewards) {
            total_remain += Number(r.remain);
            ids.push(r.id);
        }
        if (ids.length === 0) {
            // 没有新奖励，code: 1
            return res.send({
                code: 1,
                total_remain: 0
            });
        }
        // 标记这些奖励为已通知
        await markRewardsAsNotified(ids);
        // 返回有奖励的结果
        return res.json({
            code: 0,
            total_remain
        });
    } catch (e) {
        console.error('用户奖励未读累计查询失败:', e);
        return res.status(500).json({
            code: 500,
            msg: '服务器错误'
        });
    }
});


// 统计总览接口
router.post('/dashboardUserStats', async (req, res) => {
    try {
        // 1. 计算当天零点和明天零点时间戳
        const now = new Date();
        now.setHours(0, 0, 0, 0);
        const todayStart = now.getTime();
        const tomorrow = new Date(now);
        tomorrow.setDate(now.getDate() + 1);
        const todayEnd = tomorrow.getTime();

        // 2. 新增用户数
        const regCond = getUserBaseConditions({ isNew: true, todayStart, todayEnd });
        const [newUserRow] = await mysqlQuery(
            `SELECT count(*) as cnt FROM transir_users WHERE ${regCond.where}`,
            regCond.params
        );

        // 3. 今日活跃用户数
        const actCond = getUserBaseConditions({ isNew: false, todayStart, todayEnd });
        const [activeUserRow] = await mysqlQuery(
            `SELECT count(*) as cnt FROM transir_users WHERE ${actCond.where}`,
            actCond.params
        );

        // 4. 总用户
        const totalCond = getUserBaseConditions({});
        const [totalRow] = await mysqlQuery(
            `SELECT COUNT(*) as total FROM transir_users WHERE ${totalCond.where}`,
            totalCond.params
        );

        // 5. 普通会员
        const normalCond = getUserBaseConditions({ userType: 'regular' });
        const [normalRow] = await mysqlQuery(
            `SELECT COUNT(*) as count FROM transir_users WHERE ${normalCond.where}`,
            normalCond.params
        );

        // 6. 临时会员
        const tempCond = getUserBaseConditions({ userType: 'temporary_member' });
        const [tempRow] = await mysqlQuery(
            `SELECT COUNT(*) as count FROM transir_users WHERE ${tempCond.where}`,
            tempCond.params
        );

        // 7. 体验会员
        const trialCond = getUserBaseConditions({ userType: 'trial_member' });
        const [trialRow] = await mysqlQuery(
            `SELECT COUNT(*) as count FROM transir_users WHERE ${trialCond.where}`,
            trialCond.params
        );

        // 8. 正式会员
        const formalCond = getUserBaseConditions({ userType: 'renewal_member' });
        const [formalRow] = await mysqlQuery(
            `SELECT COUNT(*) as count FROM transir_users WHERE ${formalCond.where}`,
            formalCond.params
        );
        // 2.1 查询今日新注册且已使用功能的用户数
        const featureUsedTodayCond = getUserBaseConditions({ isNew: true, todayStart, todayEnd });
        const featureUsedTodaySql = `SELECT count(*) as cnt FROM transir_users WHERE ${featureUsedTodayCond.where} AND feature_used = 1`;
        const [featureUsedTodayRow] = await mysqlQuery(featureUsedTodaySql, featureUsedTodayCond.params);
        // 9. 近30天趋势（普通新增用户和活跃用户）
        const trendRows = await mysqlQuery(
            `SELECT stat_date, reg_count, active_count 
             FROM transir_daily_user_stat 
             ORDER BY stat_date DESC 
             LIMIT 30`
        );
        // (1) 转北京时间日期字符串
        const trendArr = trendRows.reverse().map(r => {
            let dateStr;
            if (typeof r.stat_date === 'string') {
                const dt = new Date(r.stat_date + 'T00:00:00Z');
                dt.setHours(dt.getHours() + 8);
                dateStr = dt.getFullYear() + '-' +
                    String(dt.getMonth() + 1).padStart(2, '0') + '-' +
                    String(dt.getDate()).padStart(2, '0');
            } else if (r.stat_date instanceof Date) {
                const dt = new Date(r.stat_date.getTime());
                dt.setHours(dt.getHours() + 8);
                dateStr = dt.getFullYear() + '-' +
                    String(dt.getMonth() + 1).padStart(2, '0') + '-' +
                    String(dt.getDate()).padStart(2, '0');
            } else {
                dateStr = r.stat_date + '';
            }
            return {
                date: dateStr,
                newUsers: r.reg_count,
                activeUsers: r.active_count
            }
        });

        // (2) 查feature_used=1的每日新增数
        const featureUsedRows = await mysqlQuery(
            `SELECT DATE(registration_time) as stat_date, COUNT(*) as featureUsedNewUsers
               FROM transir_users
              WHERE registration_time >= DATE_SUB(CURDATE(), INTERVAL 29 DAY)
                    AND feature_used = 1
              GROUP BY stat_date
              ORDER BY stat_date ASC`
        );
        // 转成映射表，方便合并
        const featureUsedMap = {};
        featureUsedRows.forEach(r => {
            // 日期需同last30Trend格式，防止类型不一致
            let dateStr;
            if (typeof r.stat_date === 'string') {
                const dt = new Date(r.stat_date + 'T00:00:00Z');
                dt.setHours(dt.getHours() + 8);
                dateStr = dt.getFullYear() + '-' +
                    String(dt.getMonth() + 1).padStart(2, '0') + '-' +
                    String(dt.getDate()).padStart(2, '0');
            } else if (r.stat_date instanceof Date) {
                const dt = new Date(r.stat_date.getTime());
                dt.setHours(dt.getHours() + 8);
                dateStr = dt.getFullYear() + '-' +
                    String(dt.getMonth() + 1).padStart(2, '0') + '-' +
                    String(dt.getDate()).padStart(2, '0');
            } else {
                dateStr = r.stat_date + '';
            }
            featureUsedMap[dateStr] = Number(r.featureUsedNewUsers) || 0;
        });

        // (3) 组装final趋势结果
        const last30Trend = trendArr.map(item => ({
            ...item,
            featureUsedNewUsers: featureUsedMap[item.date] || 0
        }));

        // 10. 汇总返回
        res.json({
            code: 0,
            msg: 'ok',
            data: {
                today: {
                    activeUsers: activeUserRow.cnt,    // 今日活跃
                    newUsers: newUserRow.cnt,          // 今日新增
                    featureUsedNewUsers: featureUsedTodayRow.cnt
                },
                userStats: {
                    total: totalRow.total,
                    normalMembers: normalRow.count,
                    tempMembers: tempRow.count,
                    trialMembers: trialRow.count,
                    formalMembers: formalRow.count
                },
                last30Trend
            }
        });
    } catch (err) {
        console.error('统计接口异常', err);
        res.status(500).json({ code: 500, msg: '服务异常' });
    }
});

router.post('/dashboardMomoStats', async (req, res) => {
    try {
        // -- 获取北京时间当天0点和明天0点
        const now = new Date();
        now.setHours(0, 0, 0, 0);
        const todayStart = new Date(now);
        const tomorrow = new Date(now);
        tomorrow.setDate(now.getDate() + 1);

        // --- 今日系统发放（赠送type=2,3） ---
        const [todayGrantRow] = await mysqlQuery(
            `SELECT IFNULL(SUM(amount),0) as todayGrantMomo
             FROM transir_recharge_records
             WHERE type IN ('2','3')
               AND recharge_time >= ?
               AND recharge_time < ?`,
            [todayStart, tomorrow]
        );

        // --- 今日充值(type=1) ---
        const [todayRechargeRow] = await mysqlQuery(
            `SELECT IFNULL(SUM(amount),0) as todayRechargeMomo
             FROM transir_recharge_records
             WHERE type = '1' AND pk = 'com.trans.translationAiAndSubtitle' AND (env IS NULL OR env != 'sanbox')
               AND recharge_time >= ?
               AND recharge_time < ?`,
            [todayStart, tomorrow]
        );

        // --- 当前所有用户可用momo币总和（未用完、未过期、未通知) ---
        const [totalAvailableRow] = await mysqlQuery(
            `SELECT IFNULL(SUM(remain),0) as totalAvailableMomo
             FROM transir_recharge_records
             WHERE remain > 0 AND pk != 'com.TranslationAISubTitle'
               AND status = 'unused'
               AND (expire_time IS NULL OR expire_time > NOW())`
        );

        // --- 近30天趋势 每天的充值量/赠送量 ---
        const trendRows = await mysqlQuery(`
            SELECT 
    DATE(recharge_time) AS d,
    SUM(CASE WHEN type='1' AND pk='com.trans.translationAiAndSubtitle' AND (env IS NULL OR env != 'sanbox') THEN amount ELSE 0 END) AS rechargeAmount,
    SUM(CASE WHEN type IN ('2','3') THEN amount ELSE 0 END) AS grantAmount
FROM transir_recharge_records
WHERE recharge_time >= DATE_SUB(CURDATE(), INTERVAL 29 DAY)
GROUP BY d
ORDER BY d ASC
        `);

        // 统一格式化日期为北京时间yyyy-MM-dd
        const last30Trend = trendRows.map(r => {
            // 这里r.d是日期字符串 2025-07-25
            let dateStr;
            if (typeof r.d === 'string') {
                const dt = new Date(r.d + 'T00:00:00Z');
                dt.setHours(dt.getHours() + 8);
                dateStr = dt.getFullYear() + '-' +
                    String(dt.getMonth() + 1).padStart(2, '0') + '-' +
                    String(dt.getDate()).padStart(2, '0');
            } else if (r.d instanceof Date) {
                const dt = new Date(r.d.getTime());
                dt.setHours(dt.getHours() + 8);
                dateStr = dt.getFullYear() + '-' +
                    String(dt.getMonth() + 1).padStart(2, '0') + '-' +
                    String(dt.getDate()).padStart(2, '0');
            } else {
                dateStr = r.d + '';
            }
            return {
                date: dateStr,
                rechargeAmount: Number(r.rechargeAmount) || 0,
                grantAmount: Number(r.grantAmount) || 0
            }
        });

        res.json({
            code: 0,
            msg: 'ok',
            data: {
                todayGrantMomo: Number(todayGrantRow.todayGrantMomo) || 0,
                todayRechargeMomo: Number(todayRechargeRow.todayRechargeMomo) || 0,
                totalAvailableMomo: Number(totalAvailableRow.totalAvailableMomo) || 0,
                last30Trend
            }
        });
    } catch (err) {
        console.error('dashboardMomoStats error:', err);
        res.status(500).json({ code: 1, msg: '获取momo币统计失败', error: err.message });
    }
});


// 统一过滤（长度>=10且不在禁止名单，时间限制）
function getUserBaseConditions({ isNew, todayStart, todayEnd, userType }) {
    const conditions = [];
    const values = [];
    conditions.push("LENGTH(apple_identifier) >= 10");
    const exclude = [
        '000672.0e4740c4414441b2be8fba92997a789e.0324',
        '000672.b8b722bc0df24a4d95afa7aeadc340f0.1029',
        '000448.ab3b7bfdc4c245daba6a0de0948e5a1b.0418'
    ];
    if (exclude.length) {
        conditions.push(`apple_identifier NOT IN (${exclude.map(() => '?').join(',')})`);
        values.push(...exclude);
    }
    // === 时间范围判空再拼 ===
    if (
        typeof todayStart === 'number' && !isNaN(todayStart) &&
        typeof todayEnd === 'number' && !isNaN(todayEnd) &&
        isNew !== undefined
    ) {
        if (isNew) {
            conditions.push("registration_time >= FROM_UNIXTIME(?)");
            conditions.push("registration_time < FROM_UNIXTIME(?)");
        } else {
            conditions.push("last_login_time >= FROM_UNIXTIME(?)");
            conditions.push("last_login_time < FROM_UNIXTIME(?)");
        }
        values.push(todayStart / 1000, todayEnd / 1000);
    }
    if (userType) {
        conditions.push("user_type = ?");
        values.push(userType);
    }
    return { where: conditions.join(' AND '), params: values };
}
//后台接口 每日订阅
router.post('/todayCount', async (req, res) => {
    try {
        const sql = `
            SELECT
                SUM(CASE WHEN notification_type IN ('DID_RENEW', 'SUBSCRIBED') THEN 1 ELSE 0 END) AS subscribeCount,
                SUM(CASE WHEN notification_type = 'ONE_TIME_CHARGE' THEN 1 ELSE 0 END) AS oneTimeChargeCount
            FROM js_app_apple_notifications
            WHERE 
                pk = ?
                AND environment = ?
                AND create_date >= CURDATE()
                AND create_date < CURDATE() + INTERVAL 1 DAY
        `;
        const params = [
            'com.trans.translationAiAndSubtitle',
            'Production'
        ];

        const results = await mysqlQuery(sql, params);

        res.json({
            code: 0,
            msg: '查询成功',
            data: {
                subscribeCount: results[0]?.subscribeCount || 0,
                oneTimeChargeCount: results[0]?.oneTimeChargeCount || 0
            }
        });
    } catch (err) {
        res.status(500).json({
            code: 1,
            msg: '服务器内部错误',
            error: err.message
        });
    }
});
//后台接口 今日各功能使用情况
router.post('/todayUsageStats', async (req, res) => {
    try {
        const ALL_FEATURE_TYPES = [
            'accurate_minute',
            'quick_translate_minute',
            'comic_realtime_minute',      // 新增：漫画实时翻译秒数
            'redeem_tokens',
            'web_capture',
            'quick_capture',
            'float_copy',
            'quick_text',
            'video_export',
            'interpretation_refined',
            'interpretation_quick',
        ];

        // 1. 今日使用数据
        const sqlToday = `
            SELECT
                feature_type,
                COUNT(DISTINCT user_id) AS userCount,
                SUM(usage_amount) AS totalUsageAmount,
                SUM(usage_count) AS totalUsageCount,
                IFNULL(ROUND(SUM(usage_amount)/COUNT(DISTINCT user_id),2),0) AS avgUsageAmount,
                IFNULL(ROUND(SUM(usage_count)/COUNT(DISTINCT user_id),2),0) AS avgUsageCount
            FROM
                transir_usage_logs
            WHERE
                created_date = CURDATE()
            GROUP BY
                feature_type
        `;
        const todayRows = await mysqlQuery(sqlToday, []);

        // 2. 总使用数据
        const sqlTotal = `
            SELECT
                feature_type,
                SUM(usage_amount) AS totalAllUsageAmount,
                SUM(usage_count) AS totalAllUsageCount
            FROM
                transir_usage_logs
            GROUP BY
                feature_type
        `;
        const totalRows = await mysqlQuery(sqlTotal, []);

        // 3. 做成Map，便于类型快速映射
        const todayMap = {};
        todayRows.forEach(r => {
            todayMap[r.feature_type] = {
                featureType: r.feature_type,
                userCount: Number(r.userCount),
                totalUsageAmount: Number(r.totalUsageAmount) || 0,
                totalUsageCount: Number(r.totalUsageCount) || 0,
                avgUsageAmount: Number(r.avgUsageAmount) || 0,
                avgUsageCount: Number(r.avgUsageCount) || 0
            };
        });

        const totalMap = {};
        totalRows.forEach(r => {
            totalMap[r.feature_type] = {
                totalAllUsageAmount: Number(r.totalAllUsageAmount) || 0,
                totalAllUsageCount: Number(r.totalAllUsageCount) || 0
            };
        });

        // 转分钟辅助函数
        function toMin(val) {
            return Math.round((val / 60) * 100) / 100;
        }

        // 补全所有类型，单日+总计合并
        const allStats = ALL_FEATURE_TYPES.map(ftype => {
            let today = todayMap[ftype] || {
                featureType: ftype,
                userCount: 0,
                totalUsageAmount: 0,
                totalUsageCount: 0,
                avgUsageAmount: 0,
                avgUsageCount: 0
            };
            let total = totalMap[ftype] || {
                totalAllUsageAmount: 0,
                totalAllUsageCount: 0
            };
            // 转分钟
            if (['accurate_minute', 'quick_translate_minute', 'comic_realtime_minute', 'interpretation_refined', 'interpretation_quick'].includes(ftype)) {
                today.totalUsageAmount = toMin(today.totalUsageAmount);
                today.avgUsageAmount = toMin(today.avgUsageAmount);
                total.totalAllUsageAmount = toMin(total.totalAllUsageAmount);
            }
            return {
                ...today,
                ...total
            }
        });

        res.json({ code: 0, msg: 'ok', data: allStats });
    } catch (err) {
        res.status(500).json({ code: 1, msg: '今日功能使用统计失败', error: err.message });
    }
});

router.post('/usageStatsTrend', async (req, res) => {
    try {
        const ALL_FEATURE_TYPES = [
            'accurate_minute',
            'quick_translate_minute',
            'comic_realtime_minute',      // 新增：漫画实时翻译秒数cd
            'redeem_tokens',
            'web_capture',
            'quick_capture',
            'float_copy',
            'quick_text',
            'video_export',
            'interpretation_refined',
            'interpretation_quick',
        ];

        // 1. 查近30天真实所有有效数据
        const sql = `
            SELECT stat_date, feature_type, total_usage_amount
            FROM transir_daily_usage_stats
            WHERE stat_date >= DATE_SUB(CURDATE(), INTERVAL 29 DAY)
            ORDER BY stat_date ASC, feature_type ASC
        `;
        const rows = await mysqlQuery(sql, []);

        // 2. 收集所有有效日期（有数据的天），升序排列
        const dateSet = new Set();
        rows.forEach(r => {
            let d = r.stat_date instanceof Date
                ? `${r.stat_date.getFullYear()}-${String(r.stat_date.getMonth() + 1).padStart(2, '0')}-${String(r.stat_date.getDate()).padStart(2, '0')}`
                : r.stat_date;
            dateSet.add(d);
        });
        const dateArr = Array.from(dateSet).sort();

        // 3. 生成 feature_type => { 日期 => 用量 } map
        const dataMap = {};
        ALL_FEATURE_TYPES.forEach(ftype => { dataMap[ftype] = {}; });
        rows.forEach(r => {
            let d = r.stat_date instanceof Date
                ? `${r.stat_date.getFullYear()}-${String(r.stat_date.getMonth() + 1).padStart(2, '0')}-${String(r.stat_date.getDate()).padStart(2, '0')}`
                : r.stat_date;
            let val = Number(r.total_usage_amount) || 0;
            // 翻译矩阵单位换分钟
            if (['accurate_minute', 'quick_translate_minute', 'comic_realtime_minute', "interpretation_refined", "interpretation_quick"].includes(r.feature_type)) {
                val = Math.round(val / 60 * 100) / 100;
            }
            dataMap[r.feature_type][d] = val;
        });

        // 4. 补全每个功能的数据
        const trends = ALL_FEATURE_TYPES.map(ftype => ({
            featureType: ftype,
            data: dateArr.map(d => dataMap[ftype][d] || 0)
        }));

        res.json({
            code: 0,
            msg: 'ok',
            data: { dateArr, trends }
        });
    } catch (err) {
        res.status(500).json({ code: 1, msg: '获取功能使用趋势失败', error: err.message });
    }
});
router.post('/momoConsumeTrend', async (req, res) => {
    try {
        const ALL_FEATURE_TYPES = [
            'accurate_minute',
            'quick_translate_minute',
            'comic_realtime_minute',
            'redeem_tokens',
            'web_capture',
            'quick_capture',
            'float_copy',
            'quick_text',
            'interpretation_refined',
            'interpretation_quick',
        ];
        // 1. 近30天，聚合每天每种功能的消耗momo数量
        const sql = `
            SELECT DATE(consume_time) AS stat_date, type AS feature_type, SUM(consume_amount) AS total_momo
            FROM transir_consume_records
            WHERE consume_time >= DATE_SUB(CURDATE(), INTERVAL 29 DAY)
            GROUP BY stat_date, feature_type
            ORDER BY stat_date ASC, feature_type ASC
        `;
        const rows = await mysqlQuery(sql, []);

        // 2. 收集全部有数据的日期
        const dateSet = new Set();
        rows.forEach(r => {
            let d = r.stat_date instanceof Date
                ? `${r.stat_date.getFullYear()}-${String(r.stat_date.getMonth() + 1).padStart(2, '0')}-${String(r.stat_date.getDate()).padStart(2, '0')}`
                : r.stat_date;
            dateSet.add(d);
        });
        const dateArr = Array.from(dateSet).sort();

        // 3. 构建 feature_type -> 日期 -> momo用量的map
        const dataMap = {};
        ALL_FEATURE_TYPES.forEach(ftype => { dataMap[ftype] = {}; });
        rows.forEach(r => {
            let d = r.stat_date instanceof Date
                ? `${r.stat_date.getFullYear()}-${String(r.stat_date.getMonth() + 1).padStart(2, '0')}-${String(r.stat_date.getDate()).padStart(2, '0')}`
                : r.stat_date;
            let val = Number(r.total_momo) || 0;
            dataMap[r.feature_type][d] = val;
        });

        // 4. 每个功能补全每一天的数据
        const trends = ALL_FEATURE_TYPES.map(ftype => ({
            featureType: ftype,
            data: dateArr.map(d => dataMap[ftype][d] || 0)
        }));

        res.json({
            code: 0,
            msg: 'ok',
            data: { dateArr, trends }
        });
    } catch (err) {
        res.status(500).json({ code: 1, msg: '获取MOMO币功能消耗趋势失败', error: err.message });
    }
});

//多模型提示词管理
function normalizeVersion(version) {
    if (typeof version !== 'string') throw new Error('版本号必须为字符串');

    const cleanVer = version.replace(/^v/i, '');
    const parts = cleanVer.split('.').slice(0, 3);
    if (parts.some(p => !/^\d+$/.test(p))) {
        throw new Error(`非法版本号格式: ${version}`);
    }
    while (parts.length < 3) parts.push('0');
    return parts.map(p => p.padStart(6, '0')).join('.');
}

router.post('/modelConfig/list', async (req, res) => {
    let { page = 1, pageSize = 10, version } = req.body;
    page = parseInt(page) || 1;
    pageSize = parseInt(pageSize) || 10;
    const offset = (page - 1) * pageSize;

    let whereClauses = ['status = 1'];
    let params = [];

    try {
        if (version) {
            try {
                const normVer = normalizeVersion(version);
                whereClauses.push(`min_version_norm <= ?`);
                whereClauses.push(`max_version_norm >= ?`);
                params.push(normVer, normVer);
            } catch (err) {
                return res.status(400).json({ code: 400, msg: err.message });
            }
        }

        const whereSQL = whereClauses.length ? `WHERE ${whereClauses.join(' AND ')}` : '';

        const totalRows = await mysqlQuery(
            `SELECT COUNT(*) as total FROM transir_feature_config ${whereSQL}`,
            params
        );
        const listRows = await mysqlQuery(
            `SELECT * FROM transir_feature_config ${whereSQL} ORDER BY id DESC LIMIT ?, ?`,
            [...params, offset, pageSize]
        );

        // 时间、JSON字段处理
        const list = listRows.map(row => {
            // 格式化时间字段为北京时间
            ['created_at', 'updated_at'].forEach(fld => {
                if (row[fld]) {
                    // 确保转为北京时间
                    row[fld] = moment(row[fld]).tz('Asia/Shanghai').format('YYYY-MM-DD HH:mm:ss');
                }
            });
            // feature_list字段主动parse
            if (row.feature_list && typeof row.feature_list === 'string') {
                try {
                    row.feature_list = JSON.parse(row.feature_list);
                } catch (e) {
                    row.feature_list = []; // 或者null
                }
            }
            return row;
        });

        return res.send({
            code: 0,
            msg: '获取成功',
            data: {
                list,
                total: totalRows[0].total
            }
        });
    } catch (e) {
        console.error('[modelConfig/list] 获取失败:', e);
        res.status(500).json({ code: 500, msg: '服务器异常' });
    }
});
router.post('/modelConfig/getLatest', async (req, res) => {
    let { version, pk } = req.body;
    if (!version) {
        return res.status(400).json({ code: 400, msg: 'version为必传参数' });
    }
    if (!pk) {
        return res.status(400).json({ code: 400, msg: 'pk为必传参数' });
    }
    // 假定normalizeVersion可正常调用
    let versionNorm;
    try {
        versionNorm = normalizeVersion(version);
    } catch (e) {
        return res.status(400).json({ code: 400, msg: e.message || 'version格式非法' });
    }

    try {
        // 查可用的，按id倒序1条
        const rows = await mysqlQuery(
            `SELECT * FROM transir_feature_config 
             WHERE status=1 AND pk=? AND min_version_norm <= ? AND max_version_norm >= ? 
             ORDER BY id DESC LIMIT 1`,
            [pk, versionNorm, versionNorm]
        );
        if (!rows.length) {
            return res.send({ code: 0, msg: '暂无配置', data: [] });
        }
        const row = rows[0];

        // 格式化时间（北京时间）
        ['created_at', 'updated_at'].forEach(fld => {
            if (row[fld]) {
                row[fld] = moment(row[fld]).tz('Asia/Shanghai').format('YYYY-MM-DD HH:mm:ss');
            }
        });

        // feature_list转JSON
        if (row.feature_list && typeof row.feature_list === 'string') {
            try {
                row.feature_list = JSON.parse(row.feature_list);
            } catch (e) {
                row.feature_list = [];
            }
        }

        // 去掉多余字段
        delete row.min_version_norm;
        delete row.max_version_norm;
        delete row.status;

        return res.send({
            code: 0,
            msg: '获取成功',
            data: row
        });
    } catch (e) {
        console.error('[modelConfig/getLatest] 失败:', e);
        res.status(500).json({ code: 500, msg: '服务器异常' });
    }
});
router.post('/modelConfig/switchEnv', async (req, res) => {
    const { id, pk } = req.body;
    if (!id || !pk) {
        return res.status(400).json({ code: 400, msg: 'id 和 pk 必填' });
    }
    try {
        const rows = await mysqlQuery('SELECT id FROM transir_feature_config WHERE id=?', [id]);
        if (!rows.length) {
            return res.status(404).json({ code: 404, msg: '该配置不存在' });
        }
        await mysqlQuery('UPDATE transir_feature_config SET pk=? WHERE id=?', [pk, id]);
        res.send({ code: 0, msg: '环境切换成功' });
    } catch (e) {
        res.status(500).json({ code: 500, msg: '环境切换失败' });
    }
});
/**
 * 保存配置（有 id 就更新，没有 id 就新增）
 * POST /modelConfig/save
 * 参数：
 *  - id (可选, 有则更新, 无则新增)
 *  - min_version, max_version, feature_list, status (status 默认 1)
 */
router.post('/modelConfig/save', async (req, res) => {
    let { id, min_version, max_version, feature_list, status = 1 } = req.body;

    if (!min_version || !max_version || !feature_list) {
        return res.status(400).json({ code: 400, msg: 'min_version, max_version, feature_list 为必填' });
    }

    try {
        // feature_list 转字符串
        if (typeof feature_list !== 'string') {
            feature_list = JSON.stringify(feature_list);
        }

        // 版本号规范化存储
        let min_version_norm, max_version_norm;
        try {
            min_version_norm = normalizeVersion(min_version);
            max_version_norm = normalizeVersion(max_version);
        } catch (err) {
            return res.status(400).json({ code: 400, msg: err.message });
        }

        if (id) {
            // 更新
            const rows = await mysqlQuery('SELECT id FROM transir_feature_config WHERE id = ?', [id]);
            if (!rows.length) {
                return res.status(404).json({ code: 404, msg: '配置不存在' });
            }
            await mysqlQuery(
                `UPDATE transir_feature_config 
                 SET min_version=?, min_version_norm=?, max_version=?, max_version_norm=?, feature_list=?, status=?
                 WHERE id=?`,
                [min_version, min_version_norm, max_version, max_version_norm, feature_list, status, id]
            );
            return res.send({ code: 0, msg: '修改成功' });
        } else {
            // 新增
            await mysqlQuery(
                `INSERT INTO transir_feature_config 
                 (min_version, min_version_norm, max_version, max_version_norm, feature_list, status) 
                 VALUES (?, ?, ?, ?, ?, ?)`,
                [min_version, min_version_norm, max_version, max_version_norm, feature_list, status]
            );
            return res.send({ code: 0, msg: '新增成功' });
        }
    } catch (e) {
        console.error('[modelConfig/save] 失败:', e);
        res.status(500).json({ code: 500, msg: '服务器异常' });
    }
});

/**
 * 4. 删除配置
 * POST /config/delete
 * 参数：id
 */
router.post('/modelConfig/delete', async (req, res) => {
    const { id } = req.body;
    if (!id) {
        return res.status(400).json({ code: 400, msg: 'id必传' });
    }

    try {
        const rows = await mysqlQuery('SELECT id FROM transir_feature_config WHERE id = ?', [id]);
        if (!rows.length) {
            return res.status(404).json({ code: 404, msg: '配置不存在或已删除' });
        }

        await mysqlQuery('DELETE FROM transir_feature_config WHERE id = ?', [id]);

        return res.send({ code: 0, msg: '删除成功' });
    } catch (e) {
        console.error('[config/delete] 失败:', e);
        res.status(500).json({ code: 500, msg: '服务器异常' });
    }
});


module.exports = router;